MySQL slow log even after indexing of the columns

Question

I am using a mysql server in which I have set slow query log with following settings

log-slow-queries=/var/log/mysql/mysql-slow-queries.log
long_query_time=1
log-queries-not-using-indexes

I have set following event for every minute in mysql event.

UPDATE  mytable SET playing = 0
    WHERE  playing != 0
      AND  ( TIMESTAMPDIFF( MINUTE , lastplayed, NOW( )) >10 )
       OR  ( lastplayed IS NULL
              AND  ispresent= 0
           );

Now all these columns playing, lastplayed, and ispresent are indexed but still this is appearing in slow query log with following details

# Query_time: 0.000585  Lock_time: 0.000159 Rows_sent: 0  Rows_examined: 316

Why this query is showing in slow log?

Suspect the main reason is that you have an OR, with the OR clauses having not quite the same columns. Further I suspect you have an operator precedence issue with the AND and the OR (ie, the _playing != 0_ only applies to the first timestamp heck). — Kickstart, Dec 23 '15 at 13:20
It took less than a millisecond, so it is very fast. Do you expect there to be millions of rows in the table some day? If so, then you should care. — Rick James, Dec 24 '15 at 02:49

Tobias Baumeister · Answer 1 · 2015-12-23T11:41:33.533

My guess is that when you use lastplayed in a function (in this case TIMESTAMPDIFF), the index cannot be used. MySQL is not smart enough to understand that you are looking for all lastplayed stamps which are older than 10 minutes. Therefore it goes over ALL rows.

Instead you can use this:

... AND lastplayed < DATE_SUB(NOW(), INTERVAL 10 MINUTE) ...

Then MySQL is calculating the DATE_SUB only ONCE and can then compare lastplayed with an actual integer value. This way MySQL is smart enough again to use indexes.

DETAILED REASON:
Imagine you write your own MySQL function (pseudo code):

function weirdFunction(Integer i): {
    if (RAND() < 0.5) return i;
    else return 0;
}

Now when you want to run your query like:

... AND weirdFunction(lastplayed) = 0 ...

how is MySQL supposed to know which lastplayed values will give which result? It can't. It needs to perform the function for ALL rows in order to find out. And therefore no index can be used.

score 0 · Answer 2 · answered Dec 24 '15 at 02:55

Get rid of the OR by doing two UPDATEs, one for each side of the OR:

UPDATE  mytable SET playing = 0
    WHERE  lastplayed < NOW() - INTERVAL 10 MINUTE;
UPDATE  mytable SET playing = 0
    WHERE  lastplayed IS NULL
      AND  ispresent= 0;

(That is assuming you have the precedence right.)

Those will need and use this one "composite" index:

INDEX(lastplayed, ispresent)

(Individual indexes on each column won't be as efficient.)

MySQL slow log even after indexing of the columns

2 Answers2