CODEWITHSUNDEEP
mysqlsqlperformance
silentman.it
silentman.it

Reputation: 361

Improving MySQL SELECT WHERE datetime query performance

I'm facing performance issues querying a table structured like this:

CREATE TABLE `SerieDetailSet`(
`Id` int NOT NULL AUTO_INCREMENT UNIQUE, 
`TimeStamp` datetime NOT NULL, 
`DoubleValue` double, 
`StringValue` longtext, 
`ValidityStartDate` datetime NOT NULL, 
`ValidityEndDate` datetime NOT NULL, 
`ValidityStartOffset` int NOT NULL, 
`ValidityEndOffset` int NOT NULL, 
`Serie_Id` int NOT NULL, 
`SerieDetailGroup_Id` int NOT NULL);

ALTER TABLE `SerieDetailSet` ADD PRIMARY KEY (Id);

Serie_Id and SerieDetailGroup_Id are foreign keys and they are indexed

I've also added an index to ValidityStartDate and ValidityEndDate columns

ALTER TABLE `seriedetailset` ADD INDEX `ValidityStartDate_ValidityEndDate` (`ValidityStartDate`, `ValidityEndDate`);

The table is pretty fat (~20M rows) and my typical SELECT query is

SELECT * FROM `seriedetailset`
WHERE `Serie_Id` IN ( 109,110,111,112,113 ) AND  `ValidityStartDate` >= '2013-12-25 00:00:00' AND `ValidityStartDate` < '2013-12-25 13:15:00'

What do I have to do to improve query performance? Now It's not acceptable

EDIT

Explain for a typical query

EXPLAIN
SELECT *
FROM `SerieDetailSet`
WHERE ((`ValidityStartDate` >= '01/01/2013 00:45:00') AND (`ValidityStartDate` < '[01/01/2013 01:00:00]')) AND (`Serie_Id` IN ( 109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,155 ))

+------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+
| id   | select_type | table          | type | possible_keys          | key  | key_len | ref  | rows     | Extra       |
+------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+
|    1 | SIMPLE      | SerieDetailSet | ALL  | IX_FK_SerieSerieDetail | NULL | NULL    | NULL | 16440103 | Using where |
+------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+

Upvotes: 1

Views: 1775

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

If this is your query:

SELECT *
FROM `seriedetailset`
WHERE `Serie_Id` IN ( 109,110,111,112,113 ) AND 
      `ValidityStartDate` >= '2013-12-25 00:00:00' AND `ValidityStartDate` < '2013-12-25 13:15:00'

Then the appropriate index is a composite index:

create index idx_seriedetailset_id_sd on seriedetailset(serie_id, validitystartdate);

Upvotes: 1

Related Questions