CODEWITHSUNDEEP
mysqlfull-text-searchquery-optimization
MoreScratch
MoreScratch

Reputation: 3083

MySQL multiple OR's in WHERE

I have the following query:

SELECT
    b.business_name,
    b.address,
    b.city,
    b.state,
    b.phone_number,
    i.date
FROM business_table b
LEFT JOIN inspection_table i ON b.id = i.business_id
WHERE i.date = (
    SELECT MAX(i.date)
    FROM inspection_table i
    WHERE b.id = i.business_id
)
AND b.city LIKE '%Boston%'
OR b.city LIKE '%Dallas%'
OR b.city LIKE '%Seattle%'
OR b.city LIKE '%New York%'
OR b.city LIKE '%Portland%'
OR b.city LIKE '%San Antonio%'
OR b.city LIKE '%Los Angeles%'
OR b.city LIKE '%Miami%'
ORDER BY i.date DESC;

I get the result I am after but the query is very slow (~17s). Is there a better way to construct this query? The city field is indexed in the business_table.

Upvotes: 0

Views: 70

Answers (3)

Rick James
Rick James

Reputation: 142518

There seem to be multiple problems in the formulation of the query. The other answers have brought out some of the fixes. I'll try to address all the problems.

Parentheses is a must -- x AND y OR z OR w is treated as (x AND y) OR z OR w. This may fix the intent and the performance.

You are looking for South Boston as well as Boston? Also Boston Heights? If not, then get rid of the wildcards (%). This will make it possible to index city.

If you do need the wild cards, then using a FULLTEXT index and MATCH...AGAINST... will run a lot faster.

With the leading %, I think, the REGEXP suggested Obsidian Age will be faster because it would do only one scan of city.

If business_id is not the PRIMARY KEY, add these:

INDEX(business_id, date)
INDEX(business_id, city)

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15961

I have a feeling your OR is wrong, so this is what I think will be the better query.

SELECT
    b.business_name,
    b.address,
    b.city,
    b.state,
    b.phone_number,
    li.lastInspection AS `date`
FROM (SELECT business_id, MAX(i.date) as lastInspection
      FROM inspection_table
      GROUP BY business_id) AS li
INNER JOIN business_table b
ON li.business_id = b.business_id
WHERE b.city LIKE '%Boston%'
   OR b.city LIKE '%Dallas%'
   OR b.city LIKE '%Seattle%'
   OR b.city LIKE '%New York%'
   OR b.city LIKE '%Portland%'
   OR b.city LIKE '%San Antonio%'
   OR b.city LIKE '%Los Angeles%'
   OR b.city LIKE '%Miami%'
ORDER BY li.lastInspection DESC
;

If you have a huge number of cities or businesses, this might be slow; but I'd wager an index on inspection_table (business_id, date) would speed it up quite a bit.

Ideally, this will get your last inspections, find their business and then filter the businesses; if it doesn't you could force it by putting all but the WHERE conditions in a subquery, and have an outer query do the final filtering.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562871

A conventional index doesn't help LIKE '%pattern' queries at all. The index is not used, and your query must do a complete table-scan of the table.

You need to use Full-Text Search Functions if you want it to search efficiently using MySQL.

You might like my presentation Full Text Search Throwdown, in which I compare different methods of text searching for MySQL.

Upvotes: 1

Related Questions