I've got a nasty looking search query, and I'm afraid with a large table
it's going to be horribly inefficient, and I was wondering if anybody
could think of a way to slim this one down.
I'll simplify my situation as much as I can.
I'm working with two tables here. The first is a user table:
create table users(
userid integer not null primary key,
firstnames text not null,
lastname text not null
);
The second is a message board table:
create table posts(
postid integer not null primary key,
reply_to integer references posts(postid),
author integer not null references users(userid),
subject text not null,
body text not null
);
I think the only columns which need explaining are postid and reply_to.
When a new thread is created, a row is inserted into posts with a unique
postid and a null reply_to. Any subsequent responses to this original
post are also given unique postid's but their reply_to field references
the start of the thread.
I'm writing a search function. It would be useful to search by thread,
and not by individual post. So I've created an aggregate function
concat(text). I've also created a scoring function that takes in three
text variables. The first should be the query (which I'll represent as
$query, since it's provided by the webserver). The second should be the
subject to search (subject is scored differently than the body) and the
third should be the body to search.
Here's the query:
select lastname, firstnames, subject, threadid from posts, users,
(select threadid, concat(body) as thread_body from (select postid as
threadid, body from posts where reply_to is null union select reply_to
as threadid, body from posts where reply_to is not null) as
inner_subquery group by threadid) as outer_subquery where users.userid =
posts.author and threadid = postid and
score_search('$query',subject,thread_body) > 0 order by
score_search('$query',subject,thread_body);
Forgive me if I have any syntactic errors. I'm translating from my real
query. My real query's a little larger since my actualy scenario's a
little more complicated. But that's four actual select calls to produce
this. Is there a prettier way anybody can think of?
Thanks!
-jag
