MySQL将FULLTEXT与LIKE后备相结合

Question

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?

Answer 1

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.