Optimizing MySQL Fulltext Searches

 FullText Search Faster with MySQL

MATCH (col1,col2,...) AGAINST (expr [search_modifier]) search_modifier: { IN BOOLEAN MODE | WITH QUERY EXPANSION };

Example MYSQL Fulltext Search Syntax:

SELECT MATCH('Content') AGAINST ('keyword1 keyword2') as Relevance FROM table WHERE MATCH ('Content') AGAINST('+keyword1 +keyword2' IN
BOOLEAN MODE) HAVING Relevance > 0.3 ORDER BY Relevance DESC;

SELECT *,MATCH (Title,Author,Description) AGAINST ('JT McNaught' IN BOOLEAN MODE) AS relevancy FROM ccl_main WHERE MATCH (Title,Author,Description) AGAINST ('JT McNaught' IN BOOLEAN MODE) ORDER BY relevancy DESC;

Those looking to simply match a search phrase in a large group of words, try this:



SELECT * FROM data WHERE  
bighaystack LIKE ('%littleneedle%')
AND bighaystack RLIKE '[[:<:]]littleneedle[[:>:]]';



This query will produce the same result as the following query, but is roughly 20X faster.



SELECT * FROM data WHERE 
bighaystack RLIKE '[[:<:]]littleneedle[[:>:]]';



For more than one search keyword use:



haystack LIKE ('%keyword1%keyword2%')  
AND haystack RLIKE '[[:<:]]keyword1[[:>:]].*[[:<:]]word2[[:>:]]';

Blind query expansion

 Blind-Query Expansion is an automatic relevancy search, made possible by adding the string "WITH QUERY EXPANSION" after the search phrase. Blind Query expansion performs the MySQL search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search. Hence, if one of these documents contains the word "EBIZ” and the word “IQ”, the second search finds the documents that contain the word “HEAR” even if they do not contain the word “SAY”.

Another example could be searching for books by JT McNaught about eBusiness, when a user is not sure how to spell “eBusiness”. A search for “eBiz and the reluctant Search Engine” finds only “eBusiness and the Reluctant Search Engine” without query expansion. A search with query expansion finds all books with the word “eBusiness” on the second pass.

JT McNaught  
http://www.IsPopularOnline.com