SQL Query simliping

Question

I have a sql query. This query brings records which column_2 in (51, 14) and not in (54). Sorry for my bad gramer. Here is the query:

SELECT t1.'column_1'
FROM table_1 t1
WHERE t1.'column_2' IN (51, 17)
AND t1.'column_2' NOT IN (SELECT t2.'column_1' FROM table_1 t2 WHERE t2.'column_2' = 54)
AND t1.create_time BETWEEN (NOW() - INTERVAL 25 HOUR) AND (NOW() - INTERVAL 24 HOUR)
ORDER BY t1.create_time DESC 

t1 and t2 are the same table

By the number of records which have 'table_name'.'column_2' = 2 increase, this query will lose its efficiency. Is there a better way to simplify this query?

Thank you for your help.

Answer 1

The current structure of your query already looks fairly optimal, but it might benefit from the addition of indices to make it execute faster. Consider this slightly updated version, followed by the index definitions:

SELECT t1.column_1
FROM table_name t1
WHERE
    column_2 IN (51, 17) AND
    NOT EXISTS (SELECT 1 FROM table_name t2 WHERE t2.column_1 = t1.column_2 AND
                                                  t2.column_2 = 54) AND
    create_time BETWEEN (NOW() - INTERVAL 24 HOUR) AND (NOW() - INTERVAL 25 HOUR)
ORDER BY
    create_time DESC;

CREATE INDEX idx1 ON table_name (column_2, create_time);   -- for outer query
CREATE INDEX idx2 ON table_name (column_2, column_1);      -- for inner exsits query

The index idx1 above can be used to speed up the WHERE clause of the outer query. For the exists logic, we define a second index idx2 which should let MySQL quickly lookup any column_2 value in the table and decide whether or not it is a matching record.

You may run EXPLAIN on the above query before and after the tuning, to see what benefit the indices might bring.