0

I have an online search box that needs to look across many MySQL columns for a match. And it needs to handle a multi-keyword search.

Use cases:

  • I search for DP/101/R/23 (rego no)
  • I search for Johnty Winebottom (owner)
  • I search for Le Mans 1969 (mixed, history related keywords)

I get a lot of special chars so fulltext doesn't always work. So I'm splitting the keyword input apart on spaces and then looping thorugh and doing LIKE queries.

Simplified query that gets the point across (I've removed many columns): 

SELECT   `cars`.`id`, 
         `cars`.`car_id`, 
         `cars`.`date_of_build`, 
        …..
FROM     (`cars`) 
WHERE    ( 
                  `chassis_no` LIKE "DP/101/R/23" 
         OR       `chassis_no` LIKE "DP/101/R/23 %" 
         OR       `chassis_no` LIKE "% DP/101/R/23" 
         OR       `chassis_no` LIKE "% DP/101/R/23 %" 
         OR       `history` LIKE "DP/101/R/23" 
         OR       `history` LIKE "DP/101/R/23 %" 
         OR       `history` LIKE "% DP/101/R/23" 
         OR       `history` LIKE "% DP/101/R/23 %" 
        ….

In this case (rego no) it's exact so matches the LIKE without spaces on either side.

This works.. but is slow and feels wrong. Is there another way to do this that's more efficient?

EDIT:: Using REGEXP appears to work and actually is a little faster:

chassis_no` REGEXP "([ ]*)DP/101/R/23([ ]*)"

I'm not sure of a better way since fulltext fails on many of the special characters in my data.

Quadrant6
  • 1,175
  • 2
  • 15
  • 25
  • 1
    Using wildcard at the front of a like is asking for trouble. The database can only create an index when you use wildcard at the end. [Full Text-Search is probably your best option](http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) – Antarr Byrd Oct 04 '14 at 06:40
  • 1
    possible duplicate of [How to speed up SELECT .. LIKE queries in MySQL on multiple columns?](http://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns) – artm Oct 04 '14 at 06:40
  • fulltext search fails on basic special chars, and my data contains quite a few. this appears to work though: `chassis_no` REGEXP "([ ]*)DP/101/R/23([ ]*)` – Quadrant6 Oct 04 '14 at 06:50
  • 3
    I would put such data into something like ElasticSearch and run queries from there. – Ashalynd Oct 04 '14 at 06:50
  • The regular expression you are using is not equivalent to the `like` queries. Instead, it is the same as `like '%DP/101/R/23%'`. You would need `REGEXP "(^| )DP/101/R/23( |$)"` instead. –  Oct 04 '14 at 07:23
  • Why are you searching in multiple columns for the same information? I get the feeling what you're doing isn't related to data archiving (ie, `chassis_no` hasn't changed), so it should only be in one place. Perhaps you could parse some of the data for likely columns (ie, `1970-01-01` is almost certainly a date, although you don't know what it refers to without context). If your input search box can be changed to include keywords, that would also help limit the number of columns to be searched. – Clockwork-Muse Oct 04 '14 at 07:40

0 Answers0