sql - Improving MySQL SELECT WHERE datetime query performance -

sql - Improving MySQL SELECT WHERE datetime query performance -

i'm facing performance issues querying table structured 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 together primary key (id);

serie_id , seriedetailgroup_id foreign keys , indexed

i've added index validitystartdate , validityenddate columns

alter table `seriedetailset` add together index `validitystartdate_validityenddate` (`validitystartdate`, `validityenddate`);

the table pretty fat (~20m rows) , typical select query is

select * `seriedetailset` `serie_id` in ( 109,110,111,112,113 ) , `validitystartdate` >= '2013-12-25 00:00:00' , `validitystartdate` < '2013-12-25 13:15:00'

what have improve query performance? it's not acceptable

edit

explain typical query

explain select * `seriedetailset` ((`validitystartdate` >= '01/01/2013 00:45:00') , (`validitystartdate` < '[01/01/2013 01:00:00]')) , (`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 | | +------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+ | 1 | simple | seriedetailset | | ix_fk_serieseriedetail | null | null | null | 16440103 | using | +------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+

if query:

select * `seriedetailset` `serie_id` in ( 109,110,111,112,113 ) , `validitystartdate` >= '2013-12-25 00:00:00' , `validitystartdate` < '2013-12-25 13:15:00'

then appropriate index composite index:

create index idx_seriedetailset_id_sd on seriedetailset(serie_id, validitystartdate);

mysql sql performance