I have a MySQL 8 table that has around 40m rows.
I run very frequently, especially while debugging, a query that has to exclude several "names" from that table and then group by it, so I use NOT LIKE
in the WHERE
clause. Is this the most performant/wise thing to do or is there something better?
The column in question is QNAME
on this example, type varchar(48)
.
The query is not super slow, but when add this filter you clearly see a 60-90 sec time increase, because of course I believe it’s very inefficient.
SELECT
...
FROM
...
WHERE
DATE_FORMAT(CREATIONDTE, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d')
AND NOT (QNAME LIKE '%IR360%' OR QNAME LIKE '%DEAD%' OR QNAME LIKE 'SYSTEM%' OR QNAME LIKE '%.BO')
GROUP BY
QNAME
2
Answers
An index on CREATIONDTE might improve the performance of this particular query if you don’t already have one and your selected date represents only a small fraction of the whole table.
You say the LIKEs increases the execution time, but not from what. You also don’t give an estimate of the proportion of the table or result set these constraint exclude. If the computation of the LIKEs is slowing it down significantly, maybe you could try applying the LIKEs after returning the GROUPed BY results. E.g.
"An index on CREATIONDTE" will not help because the expression is not sargable .
However, by changing
to
then the expression becomes sargable and this will be somewhat useful.
There is hope for improving the
LIKEs
.