Word Search

[The Little Guy]

Each member is allowed to post up to 40 words that describes them. I have a file that you pass a list of comma separated values, such as:

cool,cat,dog

 

this filters all the members that have those 3 words in their words list. The query I have to get those members works, but I think it is really slow since it uses having. Any thoughts on how to speed it up?

 

select *, count(*) c from words w left join users u on(w.owner = u.id) where w.word in('$filter') and u.id != $_id group by u.id having c >= $nwords limit 5

 

using the above words the query would look like this:

select *, count(*) c from words w left join users u on(w.owner = u.id) where w.word in('cool','cat','dog') and u.id != 4 group by u.id having c >= 3 limit 5

[gizmola]

What you could try instead is inner joining by user id a query for each word in the list.  The result set would only be the users who had all 3 words.. the logical equivalent of intersect of 3 seperate queries each for one of the 3 words.

[The Little Guy]

would you happen to have an example? I can't seem to wrap my head around your simple concept