Weird query in mysql optimizer

Question

I'm working with mysql 5.5.52 on a Debian 8 machine and sometimes we have a slow query (>3s) that usually spends 0.1s. I've started with the explain command to find what is happening.

This is the query and the explain info

explain
SELECT
   `box`.`message_id` ID
 , `messages`.`tipo`
 , `messages`.`text`
 , TIME_TO_SEC(TIMEDIFF(NOW(), `messages`.`date`)) `date`
FROM (`box`)
INNER JOIN `messages` ON `messages`.`id` = `box`.`message_id`
WHERE `box`.`user_id` = '1010231' AND `box`.`deleted` = 0 
    AND `messages`.`deleted` = 0 
    AND `messages`.`date` + INTERVAL 10 MINUTE > NOW()
ORDER BY `messages`.`id` ASC LIMIT 100;


id| select_type| table  | type |  possible_keys   | key   | key_len| ref          | rows | Extra    
 1|SIMPLE      |box     |ref   |user_id,message_id|user_id|       4|const         | 2200 |Using where; Using temporary; Using filesort   
 1|SIMPLE      |messages|eq_ref|PRIMARY           |PRIMARY|       4|box.message_id|    1 |Using where

I know that temporary table and filesort are a bad thing, and I suppose that the problem is that order key doesn't belong to the first table in the query (box) and changing it to box.message_id, the explain info is

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1 SIMPLE box index user_id,message_id message_id 4 443 Using where

1 SIMPLE messages eq_ref PRIMARY PRIMARY 4 box.message_id 1 Using where

It looks better, but I don't understand why it's using the message_id index, and worst, now the query takes 1.5s instead of initial 0.1s

Edit:

Forcing the query to use user_id index, I get the same result (0.1s) as the initial query but without the temporary

explain
SELECT
   `box`.`message_id` ID
 , `messages`.`tipo`
 , `messages`.`text`
 , TIME_TO_SEC(TIMEDIFF(NOW(), `messages`.`date`)) `date`
FROM (`box` use index(user_id) )
INNER JOIN `messages` ON `messages`.`id` = `box`.`message_id`
WHERE `box`.`user_id` = '1010231' AND `box`.`deleted` = 0 
    AND `messages`.`deleted` = 0 
    AND `messages`.`date` + INTERVAL 10 MINUTE > NOW()
ORDER BY `box`.`message_id` ASC LIMIT 100;

id| select_type| table  | type |  possible_keys   | key   | key_len| ref          | rows | Extra    
 1|SIMPLE      |box     |ref   |user_id,message_id|user_id|       4|const         | 2200 |Using where; Using filesort   
 1|SIMPLE      |messages|eq_ref|PRIMARY           |PRIMARY|       4|box.message_id|    1 |Using where

I think that skipping temporary is better solution than the initial query, next step is check combined index as ysth recommends.

Answer 1

it is not a good idea to calculate on fieldvalues to compare. then you get a FULL TABLE SCAN. MySQL must do it for each ROW before it can check the condition. Its better to do it on the constant piece of condition. Then MySQL can use a Index (if there one on this field)

change from

AND messages.date + INTERVAL 10 MINUTE > NOW() 

to

AND messages.date  > NOW() - INTERVAL 10 MINUTE

Answer 2

Temporary and file sort are not bad here; they are needed because using the best index (user_id) doesn't naturally produce records sorted in the order you ask for.

It's possible you might do better having a combined user_id,message_id index, but that might also end up worse. Depends on your exact data.

It isn't clear to me if you are seeing longer queries for certain user id's or the same user id sometimes taking much longer.

Update: it seems likely that having a combined index and changing order by to box.user_id,box.message_id will solve your problem, at least for users that don't have a large number of deleted messages.