CODEWITHSUNDEEP
mysqlsqlhaving
Edward Tanguay
Edward Tanguay

Reputation: 193362

How to speed up HAVING which uses a LIKE %...%

We have a query taking 30 seconds. When we remove this HAVING clause, it takes 1 second:

HAVING (group_concat(DISTINCT service.service ORDER BY 
service.service ASC SEPARATOR ",") like "%translation%")
ORDER by fqa_value DESC
LIMIT 0,10;

The field service.service is an ENUM with one of the items "translation".

Is there a more efficient way to construct this HAVING without building a comma-separated string and then searching in it as text, something like this:

HAVING ("translation" IN (DISTINCT service.service ORDER BY service.service))
ORDER by fqa_value DESC
LIMIT 0,10;

Upvotes: 2

Views: 79

Answers (4)

David Faber
David Faber

Reputation: 12485

FYI, there is a logical error in this HAVING clause:

HAVING (group_concat(DISTINCT service.service ORDER BY service.service ASC SEPARATOR ",") like "%translation%")

This will match things like atranslation, btranslation, etc. because you are not explicitly matching a starting comma. You want something more like this (the parentheses around the clause are superfluous):

HAVING CONCAT(',', group_concat(DISTINCT service.service ORDER BY service.service ASC SEPARATOR ","), ',') like "%,translation,%"

or this:

HAVING 'translation' REGEXP CONCAT('^(', group_concat(DISTINCT service.service ORDER BY service.service ASC SEPARATOR '|'), ')$')

However, I don't think either of those will significantly help your performance. Instead, what you might do is the following:

SELECT mykeycolumn, fqa_value, GROUP_CONCAT(DISTINCT service.service ORDER BY service.service ASC SEPARATOR ",")
  FROM service s1
 WHERE EXISTS ( SELECT 1 FROM service s2
                 WHERE s2.mykeycolumn = s1.mykeycolumn
                   AND s2.service = 'translation' )
 ORDER BY fqa_value DESC
 LIMIT 0,10;

where mykeycolumn is your primary key column or columns.

Upvotes: 1

user359040
user359040

Reputation:

You may find that you get better performance if you remove the HAVING clause, and instead add another join (under another alias) to the Services table with the existing join criteria, with the additional criterion service = "translation" .

Upvotes: 0

terary
terary

Reputation: 1108

"translation" IN (DISTINCT service.service ORDER BY service.service) "DISTINCT" and " ORDER BY" are not relavent and should be discarded.

I did something similar to what you are trying but I created an additional field and stuck all searchable fields in it, then search that field.

You would have to go back and change (add to) your tables, write a trigger, but it should do the trick for you

Upvotes: 0

Drathier
Drathier

Reputation: 14539

Since your LIKE starts with a wildcard you're going to have to change something fundamentally about your approach. There's no way for MySQL to index the data when you're doing full text search.

If you can alter the table:

Split the enum into bool-columns (one for each possible enum value) and index those. That's the only way you're going to get faster than O(n).

Upvotes: 0

Related Questions