Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm building a custom search page and what I need is to sort results by relevancy (eg. occurences of a keyword)

Using MySQL docs I've built this query:

SELECT ID
FROM $wpdb->posts
WHERE MATCH (post_title, post_content) AGAINST ('$keyword_list')
AND post_status = 'publish'
AND post_type = 'issue'

Problem A) If I test it using a keyword(s) that appears multiple times in my test posts, it does find those posts, but it's not sorted correctly by relevancy. For example the first found posts has around 3 occurences of the keyword while the posts returned on 3rd place has roughly 15 occurences.

Problem B) There are two posts that contain keyword "medicare", if I search for "medicare" it does return those posts, however when I search for "medi", "medic" etc. it doesn't return anything.

Is there a way to query it using MATCH/AGAINST or do I have to use LIKE % - in that case, how to sort by relevancy.

Thanks

share|improve this question
    
Had you added FULLTEXT index to your posts table? As I understand that would be required for such searches to work correctly. –  Rarst Jul 3 '11 at 16:21
    
Yeah I did, otherwise it wouldn't work at all. –  Smaug Jul 3 '11 at 19:04
    
then I think that about exhaust what can be WP-specific about this (no fulltext index by default). You might get better answers by asking this on more fitting site in the network, such as Stack Overflow. –  Rarst Jul 3 '11 at 19:10

3 Answers 3

You may want to check the Sphinx search engine http://sphinxsearch.com/

It took me about one day to set it up and learn how to use, but it does really good job with sorting by relevancy and grammar stemming. Also it is faster than fulltext search for big data sets thanks to indexing.

share|improve this answer

This won't solve problem B, but as far as problem A: I don't think full text searches automatically sort by relevance. I'm pretty sure you have to select the "AGAINST" clause AS something to even get the relevance into the results. Like:

... WHERE MATCH (post_title, post_content) AGAINST ('$keyword_list') as relevance ...
... ORDER BY relevance

But I'd also agree with Aleh: if you're looking for a sophisticated search, you'll probably need something designed for that purpose, like Sphinx.

share|improve this answer

This may or may not be important in your case, but FULLTEXT search works very strangely when there isn't very much text to search against. It's really a pain in the neck to test on a small data set. You may want to try boolean mode when you're working with a small text data set.

You should try...

 WHERE MATCH (post_title, post_content) 
       AGAINST ('keyword keyword keyword' IN BOOLEAN MODE)

@MathSmath's point about ordering by relevance is also correct.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.