Is there a better way to build this MySQL query with extensive WHERE conditions?

Question

Requirements are to take a user-provided list of keywords and return any record that has any keyword in any of 16 fields.

So if the user enters keywords of cup dog bread
(The order of the keywords is not significant and there is no implied relationship between any of them.)

So the WHERE conditions are 78 in number:

WHERE ObjObjectName LIKE '%cup%'
   OR ObjObjectName LIKE '%dog%'
   OR ObjObjectName LIKE '%bread%'
   OR ObjObjectOtherName LIKE '%cup%'
   OR ObjObjectOtherName LIKE '%dog%'
   OR ObjObjectOtherName LIKE '%bread%'
   OR ObjObjectID LIKE '%cup%'
   OR ObjObjectID LIKE '%dog%'
   OR ObjObjectID LIKE '%bread%'
   OR ObjOtherName LIKE '%cup%'
   OR ObjOtherName LIKE '%dog%'
   OR ObjOtherName LIKE '%bread%'
   OR ObjTitle LIKE '%cup%'
   OR ObjTitle LIKE '%dog%'
   OR ObjTitle LIKE '%bread%'
   OR ObjCategory LIKE '%cup%'
   OR ObjCategory LIKE '%dog%'
   OR ObjCategory LIKE '%bread%'
   OR AccCreditLineLocal LIKE '%cup%'
   OR AccCreditLineLocal LIKE '%dog%'
   OR AccCreditLineLocal LIKE '%bread%'
   OR PrimaryCreator LIKE '%cup%'
   OR PrimaryCreator LIKE '%dog%'
   OR PrimaryCreator LIKE '%bread%'
   OR EdiPublisherLocalSummary LIKE '%cup%'
   OR EdiPublisherLocalSummary LIKE '%dog%'
   OR EdiPublisherLocalSummary LIKE '%bread%'
   OR EdiPlaceOfPublication LIKE '%cup%'
   OR EdiPlaceOfPublication LIKE '%dog%'
   OR EdiPlaceOfPublication LIKE '%bread%'
   OR CreContinent LIKE '%cup%'
   OR CreContinent LIKE '%dog%'
   OR CreContinent LIKE '%bread%'
   OR CreSubContinent LIKE '%cup%'
   OR CreSubContinent LIKE '%dog%'
   OR CreSubContinent LIKE '%bread%'
   OR CreCountry LIKE '%cup%'
   OR CreCountry LIKE '%dog%'
   OR CreCountry LIKE '%bread%'
   OR CreRegion LIKE '%cup%'
   OR CreRegion LIKE '%dog%'
   OR CreRegion LIKE '%bread%'
   OR CreCounty LIKE '%cup%'
   OR CreCounty LIKE '%dog%'
   OR CreCounty LIKE '%bread%'
   OR CreStateProvince LIKE '%cup%'
   OR CreStateProvince LIKE '%dog%'
   OR CreStateProvince LIKE '%bread%'
   OR CreCity LIKE '%cup%'
   OR CreCity LIKE '%dog%'
   OR CreCity LIKE '%bread%'
   OR CreContinent1 LIKE '%cup%'
   OR CreContinent1 LIKE '%dog%'
   OR CreContinent1 LIKE '%bread%'
   OR CreSubContinent1 LIKE '%cup%'
   OR CreSubContinent1 LIKE '%dog%'
   OR CreSubContinent1 LIKE '%bread%'
   OR CreCountry1 LIKE '%cup%'
   OR CreCountry1 LIKE '%dog%'
   OR CreCountry1 LIKE '%bread%'
   OR CreRegion1 LIKE '%cup%'
   OR CreRegion1 LIKE '%dog%'
   OR CreRegion1 LIKE '%bread%'
   OR CreCounty1 LIKE '%cup%'
   OR CreCounty1 LIKE '%dog%'
   OR CreCounty1 LIKE '%bread%'
   OR CreStateProvince1 LIKE '%cup%'
   OR CreStateProvince1 LIKE '%dog%'
   OR CreStateProvince1 LIKE '%bread%'
   OR CreCity1 LIKE '%cup%'
   OR CreCity1 LIKE '%dog%'
   OR CreCity1 LIKE '%bread%'
   OR CreDateCreated LIKE '%cup%'
   OR CreDateCreated LIKE '%dog%'
   OR CreDateCreated LIKE '%bread%'
   OR CreMarkSignature LIKE '%cup%'
   OR CreMarkSignature LIKE '%dog%'
   OR CreMarkSignature LIKE '%bread%'

Is this really the best way to do this? I mean, I plan to use PDO prepared statements and I found this Q&A (LIKE query using multiple keywords from search field using PDO prepared statement) which will be very helpful, but I am relatively inexperienced with MySQL so I wasn't sure if there was better way to handle all those where conditions. Thanks!

You can use `REGEXP` to get rid of the three `LIKE`s per field: `WHERE SomeObject REGEXP 'cup|dog|bread'`. — NominSim, Mar 08 '13 at 19:01
NominSim - Thanks, I'll try that with Mikhal's additional Concat idea listed below. @biziclop-thanks for the reformat! — One4Saken, Mar 08 '13 at 20:00
Question: Is a keyword "cup" supposed to match "hiccup" as well? — Chris Travers, Mar 09 '13 at 00:05
@ChrisTravers - great question. No, preferably, it would not return "hiccup". However, this is a database of museum objects and typically there wouldn't be that many overlaps. "blue porcelain china" won't return many false positives. But I get the gist that I need to consider my use of % carefully. Thanks! — One4Saken, Mar 11 '13 at 14:42
It is an issue, because the breadbox cupboard of a 19th century doge would patch 'cup bread dog' even though it has relatively little to do with any of them. Similarly if I am looking for Chi Ro inscriptions in the Po valley, and I just type in Chi Po, I get Porcelain China. I would not expect an unusually low number of these things. My recommendation honestly is to get a special purpose text search because things like stemming are hard. — Chris Travers, Mar 11 '13 at 14:56

score 1 · Answer 1 · answered Mar 08 '13 at 19:02

1

You can use regular expression and concatenation:

WHERE CONCAT(
  ObjObjectName, 
  ObjObjectOtherName, 
  ObjObjectID, 
  ObjOtherName,
  ObjTitle,
  ObjCategory,
  AccCreditLineLocal,
  PrimaryCreator,
  EdiPublisherLocalSummary,
  EdiPlaceOfPublication,
  CreContinent,
  CreSubContinent)
  REGEXP 'cup|dog|bread'

answered Mar 08 '13 at 19:02

Mikhail Vladimirov

13,572
1
38
40

Thanks Mikhail! I'll try that. I'm wondering if this still works with a prepared statement... wherein I could use REGEXP :keywords and a $s->bindValue(':keywords', 'cup|dog|bread']); I'm off to work it. Thanks! – One4Saken Mar 08 '13 at 20:17
Would like to point out that unless you have a single word per column, you're not going to benefit from any indexes. With the original `LIKE`'s it appears that there were multiple words in a column. Where a `REGEXP` is going to be more efficient than a chain of `LIKE`'s without an index, you'd have much more benefit with an index. But this all depends on how many records you're searching. You may find it works now, but later you may run into problems. – Luke Mar 08 '13 at 21:12
@Luke - We've got 85000 records and probably will top out around 200,000... thanks for reminding me to be forward looking. Definitely don't want to revisit this 5 years down the road. – One4Saken Mar 11 '13 at 14:44

score 0 · Answer 2 · answered Mar 08 '13 at 18:54

0

You'll want to use a Full-Text search. Keep in mind that MySql's Full-Text index is limited to 4+ letter words. So "dog" would not work. You can compile MySQL and change that. Sphinx is usually better suited for smaller words and faster lookups.

I also recommend checking out MariaDB, a drop-in replacement for MySQL, created by the original MySQL developers. MariaDB has a Sphinx storage engine, which make integration easier.

answered Mar 08 '13 at 18:54

Luke

13,678
7
45
79

Thanks Luke. With my limited understanding of Full-Text search, it doesn't seem like the right solution. Two and three letter words will be valid keywords. – One4Saken Mar 08 '13 at 20:20
Well when you start getting performance issues, give Full-Text another look. – Luke Mar 08 '13 at 20:21

Is there a better way to build this MySQL query with extensive WHERE conditions?

2 Answers2