Quick way to find a word using a SQL query

My current code is to try to find 2 words “Red Table” in Title:

SELECT `id`,`title`,`colors`, `child_value`, `vendor`,`price`, `image1`,`shipping` 
FROM `databasename`.`the_table` WHERE 
`display` = '1' and  (`title` REGEXP '([[:blank:][:punct:]]|^)RED([[:blank:][:punct:]]|$)') 
                and  (`title` REGEXP '([[:blank:][:punct:]]|^)TABLE([[:blank:][:punct:]]|$)') 

The problem is, this is so slow! I even put the status “Index” to the column Title. I just want to search for multiple words in one (I would prefer in title AND description), but obviously I can’t use LIKE because it has to be separated by space or dash or start or end with that word etc.

I tried chat or something like that but phpmyadmin said function doesn’t exist. Any suggestions?

Answer

Plan A: MySQL pre-8.0: [[:<:]]RED[[:>:]] is a simplification. Those codes mean “word boundary”, which encompasses space, punctuation, and start/end.

Plan B: MySQL 8.0: \bRED\b is the new phrasing of the Plan A.

Plan C: FULLTEXT(title) with AND MATCH(title) AGAINST('+RED +TABLE' IN BOOLEAN MODE)

Plan D: If you need specifically “RED TABLE” but not “BLUE TABLE AND A RED CHAIR”, then use this technique:

  AND MATCH(title) AGAINST('+RED +TABLE' IN BOOLEAN MODE)`
  AND LIKE '%RED TABLE%';

Note on Plan D: The fulltext search is very efficient, hences it is done first. Then other clauses are applied to the few resulting rows. That is the cost of LIKE (or a similar REGEXP) is mitigated by having to check many fewer rows.

Note: We have not discussed “red table” versus “red tables”

By having suitable collation on the column title, you can either limit to the same case as the argument of have “Red” = “RED” = “red” = …

Plan E: (To further fill out the discussion): FULLTEXT(title) with AND MATCH(title) AGAINST('+"red table" IN BOOLEAN MODE) should match only “red” and “table” in that order and next to each other.

In general…

• Use ENGINE=InnoDB, not MyISAM.
• It is not really practical to set the min word len to 1 or 2. (3 is the default for InnoDB; and all settings have different names.)
• If your hosting provider does not allow any my.cnf changes, change providers.