MySQL – How to Create an Index for Timestamp

indexindex-tuningMySQLtimestamp

I have a query taking too long.

This is the table:

mysql> describe parking_data;
+----------------+--------------+------+-----+-------------------+----------------+
| Field          | Type         | Null | Key | Default           | Extra          |
+----------------+--------------+------+-----+-------------------+----------------+
| id             | int(12)      | NO   | PRI | NULL              | auto_increment |
| code           | varchar(32)  | NO   | MUL | NULL              |                |
| date_provided  | timestamp    | YES  | MUL | NULL              |                |
| available      | decimal(6,0) | YES  |     | NULL              |                |
| status_id      | decimal(3,0) | YES  |     | NULL              |                |
| status_name    | varchar(32)  | YES  |     | NULL              |                |
| status_blocked | varchar(16)  | YES  |     | NULL              |                |
| created_at     | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+----------------+--------------+------+-----+-------------------+----------------+

This is the indexes:

mysql> SHOW INDEX FROM parking_data;
+--------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| parking_data |          0 | PRIMARY      |            1 | id            | A         |     1592714 |     NULL | NULL   |      | BTREE      |         |               |
| parking_data |          1 | code         |            1 | code          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| parking_data |          1 | parking_date |            1 | date_provided | A         |      530904 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.06 sec)

This query is taking too long:

SELECT  date_provided as d,available as a
    FROM  parking_data
    WHERE  code = 'E03'
      AND  date_provided + INTERVAL '20' HOUR > NOW();
1162 rows in set (1 min 55.24 sec)

What am I doing wrong?

How can I turn my index of date_provided to be for registers only of this week?

Also, this database is inside a t1.micro machine in AWS and this is not the only system running… Also maybe can be because I have enabled SWAP on this machine…

Best Answer

First, you need to make your query SARGable. Fortunately, in your case, that is simple.

SELECT date_provided as d,available as a 
FROM parking_data 
WHERE code = 'E03' 
AND date_provided > NOW() - INTERVAL '20' HOUR

Your version of the query required the optimizer to add 20 hours to every record's date_provided, and then compare each to the value provided by NOW(). This new version of your query modifies NOW() by 20 hours, and so can use an index to find exactly where the records start that have that timestamp.

If your query is still too slow, you can add a composite index.

ALTER TABLE parking_data
ADD INDEX (`code`,`date_provided`)

This should yield extremely quick results.

You can also drop the single column index on code if you add this index, as that single column index will be redundant at that point.

Related Question