How to calculate query time to decide which one is faster or more efficient?-CodePudding

I want to compare my algorithm, here is some simplification from my case, Assume that the table is indexed using the log_timestamp column.

First query:

SELECT
    name
FROM
    user_table
WHERE
    DATE(DATETIME_ADD(log_timestamp , INTERVAL 7 HOUR)) >= DATE('2018-01-01')
    AND 
    DATE(DATETIME_ADD(log_timestamp , INTERVAL 7 HOUR)) < DATE('2019-01-01');

Second query:

SELECT
    name
FROM
    user_table
WHERE
    log_timestamp >= DATETIME_SUB('2018-01-01', INTERVAL 7 HOUR)
    AND 
    log_timestamp < DATETIME_SUB('2019-01-01', INTERVAL 7 HOUR);

Which of the two queries above would be faster and why?

CodePudding user response：

The question you asked should really be two separate questions. The first, along the lines of what you asked above, is which of the two queries is faster right now. The second question, which is really the one to consider, is how can you tune both queries to make them faster, and which one would be the fastest.

As it turns out, only the second query can use an index:

SELECT name
FROM user_table
WHERE log_timestamp >= DATETIME_SUB('2018-01-01', INTERVAL 7 HOUR) AND 
      log_timestamp < DATETIME_SUB('2019-01-01', INTERVAL 7 HOUR);

This query should benefit from an index on (log_timestamp, name). Note that your first query cannot really benefit from any index, so I expect your second query to be much faster, after the right index has been created.