Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Best way to exclude matching strings?

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
Tags:

2

Answers


  1. 0

    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.

    SELECT * FROM (
  SELECT QNAME, COUNT(*)
  FROM QNAMES
  GROUP BY QNAME
) A
WHERE A.QNAME NOT LIKE '%XYZ';
    Login or Signup to reply.
  2. 0

    "An index on CREATIONDTE" will not help because the expression is not sargable .

    However, by changing

    DATE_FORMAT(CREATIONDTE, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d')

    to

    CREATIONDTE = CURDATE()  -- if CREATIONDTE is a DATE
CREATIONDTE >= CURDATE()  -- if it is a DATETIME
CREATIONDTE >= CURDATE() AND CREATIONDTE < CURDATE + INTERVAL + 1

    then the expression becomes sargable and this will be somewhat useful.

    INDEX(CREATIONDTE)

    There is hope for improving the LIKEs.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search