Geek Answers Handbook

Optimize 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 TIMESTAMP, and it has an index on it. How can I modify this query to avoid a full table scan? (i.e. using Stamp outside the day () function)

Thank!

+5
source share
6 answers

Here's how I do it:

add additional fields: YEAR, MONTH, DAY or even HOUR, MINUTE depending on the expected traffic. Then create a trigger to fill in additional fields, possibly by subtracting the 3-hour interval first. Finally, create some index for additional fields.

+7

, (, PK) ,

ALTER TABLE Transactions ADD INDEX cover_1 (PK, Stamp)


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

( , - :))

.

+1

, - WHERE Stamp=XXXX, XXXX - . BETWEEN , WHERE Stamp BETWEEN timestamp('2010-01-01 00:00:00') AND timestamp ('2010-01-01 23:59:59') OR Stamp BETWEEN ..., , . , ​​, @petr.

+1

Stamp , , ..

1 -

2 - , Stamp > ( )

2 , .

0

, , ( 3 )? ( ), , , 6 , 6 . , , .

select *
  from transactions
 where stamp between timestamp '2010-06-01 03:00:00' and timestamp '2010-06-02 02:59:59'
    or stamp between timestamp '2010-07-01 03:00:00' and timestamp '2010-07-02 02:59:59'
    or stamp between timestamp '2010-08-01 03:00:00' and timestamp '2010-08-02 02:59:59'
    or stamp between timestamp '2010-09-01 03:00:00' and timestamp '2010-09-02 02:59:59'
    or stamp between timestamp '2010-10-01 03:00:00' and timestamp '2010-10-02 02:59:59'
    or stamp between timestamp '2010-11-01 03:00:00' and timestamp '2010-11-02 02:59:59'
    or stamp between timestamp '2010-12-01 03:00:00' and timestamp '2010-12-02 02:59:59';

NB! , . , .

0

petr , IN, MyISAM InnoDB.

MyISAM

ALTER TABLE Transactions ADD INDEX cover_1 (PK, Stamp)

Or, for InnoDB, where PK is implicitly included in each index,

ALTER TABLE Transactions ADD INDEX Stamp (Stamp)

Then

SELECT * 
FROM Transactions LEFT JOIN
  (
  SELECT PK 
  FROM Transactions 
  WHERE DAYOFMONTH(Stamp - interval 3 hour) = 1
  ) a ON Transactions.PK=a.PK

Only the index will be executed in the subquery, and the external query will pull rows only from the table in which a.PK was located.

0
source

More articles:


All Articles