2013-07-14 00:35
浏览 71


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 -


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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanjiong5023
    duanjiong5023 2013-07-14 00:49

    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
    点赞 评论
  • doumindang2416
    doumindang2416 2013-07-14 00:44

    You could concatenate the fields which will be better than searching them individually

    SELECT `id` FROM `wp_ss_images` WHERE CONCAT(`keywords`,`title`,`content`) LIKE '%cute%'

    This doesn't help with the 'greatest to least' part of your question though.

    点赞 评论