Scooter Daraf Scooter Daraf - 3 months ago 15
MySQL Question

Need Optimize TIMESTAMPDIFF query

Hi i have little long time query which takes almost 400ms .

this is my query

SELECT id, `from`
FROM messages
WHERE `to` = ?
AND `to_viewed` = '0'
AND `to_deleted` = '0'
AND TIMESTAMPDIFF(SECOND,created,?)< 20 AND TIMESTAMPDIFF(SECOND,created,?)>= 0


How can i optimize this ?

obs i have no idex .

EDIT to show my EXPLAIN

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE messages ALL created NULL NULL NULL 10 Using where

Answer

Make sure you have an index on the created column. Then use

AND created BETWEEN ? AND DATE_ADD(?, INTERVAL 20 SECOND);

An index can't be used when you perform a calculation on the column. So you should calculate the ends of the time range and then compare the column to that.