How can I avoid to use mysql function in this sql？

user_14173758 0
Viewed 36

I seem to be facing an awkward situation that use TIMESTAMPDIFF function in mysql‘s query conditions. Resoures show me it could reduce execution efficiency，but I am curious about how should I proceed.

The following is my sql

SELECT personTotal.person_id
FROM personTotal
         LEFT JOIN p2
                   ON p2.person_id = personTotal.person_id
WHERE TIMESTAMPDIFF(HOUR , personTotal.through_time, p2.through_time) < 20
mysql
1 Answers

You could phrase the WHERE clause to use addition with an INTERVAL:

SELECT p1.person_id
FROM personTotal p1
LEFT JOIN p2
    ON p2.person_id = p1.person_id
WHERE
    p1.through_time + INTERVAL 20 HOUR > p2.through_time;

The above query's WHERE clause, in addition to not using a date function, is also sargable, meaning that any index on the date columns of the two tables can be used here. The version you have above with TIMESTAMPDIFF is not sargable, and can't use an index.

user_18632290
Related