从3个可能的列中选择，按出现/相关性排序

Question

I have a table that contains 3 text fields, and an ID one.

The table exists solely to get collection of ID's of posts based on relevance of a user search.

Problem is I lack the Einsteinian intellect necessary to warp the SQL continuum to get the desired results -

SELECT `id` FROM `wp_ss_images` WHERE `keywords` LIKE '%cute%' OR `title` LIKE '%cute%' OR `content` LIKE '%cute%'

• Is this really enough to get a relevant-to-least-relevant list, or is there a better way?

Minding of course databases could be up to 20k rows, I want to keep it efficient.

---

Here is an update - I've gone the fulltext route -

EXAMPLE:

SELECT `id` FROM `wp_ss_images` WHERE MATCH (`keywords`,`title`,`content`) AGAINST ('+cute +dog' IN BOOLEAN MODE);

However it seems to be just grabbing all entries with any of the words. How can I refine this to show relevance by occurances?

Answer 1

To get a list of results based on the relevance of the number of occurrences of keywords in each field (meaning cute appears in all three fields first, then in 2 of the fields, etc.), you could do something like this:

SELECT id
FROM (
  SELECT id, 
    (keywords LIKE '%cute%') + (title LIKE '%cute%') + (content LIKE '%cute%') total 
  FROM wp_ss_images
  ) t
WHERE total > 0
ORDER BY total DESC

• SQL Fiddle Demo