doushui5587
2015-06-29 23:43
浏览 86
已采纳

MySQL将FULLTEXT与LIKE后备相结合

I'm building my app to use a single search table for searching all different object types ie: posts, pages, products etc., based on this article.

My table layout looks like so:

CREATE TABLE IF NOT EXISTS myapp_search_index (
  id int(11) unsigned NOT NULL,
  language_id int(11) unsigned NOT NULL,
  `type` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  object_id int(11) unsigned NOT NULL,
  `text` text COLLATE utf8_unicode_ci NOT NULL
  PRIMARY KEY (id,language_id),
  FULLTEXT KEY `text.fdx` (`text`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

My search query looks like so:

$items = $db->escape($query);

$query = $db->query("
    SELECT *, 
    SUM(MATCH(text) AGAINST('+{$items}' IN BOOLEAN MODE)) as score 
    FROM {$db->prefix}search_index 
    WHERE MATCH(text) AGAINST('+{$items}' IN BOOLEAN MODE) 
    GROUP BY language_id, type, object_id 
    ORDER BY score DESC 
    LIMIT " . (int)$start . ", " . (int)$limit . "
");

This works great except where we run into fulltext limitations like stop words and min word length.

For instance I have 2 entries in the table for my About Us page, one holds the page title, and one holds the content of the page.

Running the query about us returns no results as about is a stop word, and us is less than the minimum 4 letters.

So, my thought was to create a conditional fallback query using a traditional LIKE parameter as such:

if (!$query->num_rows):
    $query = $db->query("
        SELECT * 
        FROM {$db->prefix}search_index 
        WHERE text LIKE '%{$items}%' 
        GROUP BY language_id, type, object_id 
        ORDER BY id DESC 
        LIMIT " . (int)$start . ", " . (int)$limit . "
    ");
endif;

And once again this works fine. My About Us page now comes up just fine in the results.

But what I'd like is to run this all in one query and maintain the score somehow.

Is this possible?

EDIT:

Ok so in response to Michael's answer and comments I've changed my query to this:

SELECT *, 
SUM(MATCH(text) AGAINST('{$search}' IN BOOLEAN MODE)) as score 
FROM {$db->prefix}test_index 
WHERE (
    MATCH(text) AGAINST('{$search}' IN BOOLEAN MODE) 
    AND text LIKE '%{$search}%') 
OR text LIKE '%{$search}%' 
GROUP BY language_id, type, object_id 
ORDER BY score DESC

I set up a test table with 100K rows, 50K of which do contain my lorem ipsum search term.

This queries the entire table and returns results in 0.6379 microseconds without any query caching as of yet.

Can anyone tell me if this seems like a fair compromise?

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dqm74406 2015-06-30 00:18
    已采纳

    Play around with natural language mode too with multi-word:

    SELECT id,prod_name, match( prod_name )
    AGAINST ( '+harpoon +article' IN NATURAL LANGUAGE MODE) AS relevance
    FROM testproduct 
    ORDER BY relevance DESC
    

    We often just go with solr integration, throwing json csv and text files at it.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dongzhui4927 2015-07-01 01:17

    There is not a way to elegantly combine fulltext search and LIKE together to get more results.

    This is because the two predicates would have to be combined with an OR, which would in turn mean a full table scan (or full index scan if a suitable BTREE exists) is required to test the LIKE expression. All rows would have to be evaluated, which would remove any optimization you're getting from the fulltext search.

    In the opposite situation, combining MATCH and LIKE using AND instead of OR -- in cases where the fulltext match returns insufficiently precise matches -- works great because the optimizer uses the fulltext index to find all possible matching rows, then filters the identified rows against the LIKE expression. (Fulltext indexes are almost always preferred by the optimizer, when other possible query plans exist.) Unfortunately, that's the opposite of what you need.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题