Joshua Adam Ginsberg wrote:
>
> 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.
> 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);
Just thinking out loud, and it depends on how you are doing your
scoring, but what about scoring each message individually and the
grouping by threadid summing the scores? Should bring it down to two
levels plus a join with users.
- Richard Huxton