Search code examples
sqlmysqlcompound-index

Optimizing a SQL query to avoid full table scan

Consider the following query:

SELECT * FROM Transactions
WHERE day(Stamp - interval 3 hour) = 1;

The Stamp column in the Transactions table is a TIMESTAMP and there is an index on it. How could I change this query so it avoids full table scans? (that is, using Stamp outside of the day() function)

Thanks!

Solution

  • This is how I would do it:

    add some extra fields: YEAR, MONTH, DAY or even HOUR, MINUTE depending on the traffic you expect. Then build a trigger to populate the extra fields, maybe subtracting the 3 hour interval in advance. Finally build some index on the extra fields.