On a 5.5.23 server (that needs to go to 5.7 at least, I know) I have an InnoDB table with 3 columns (there are a few more that are not relevant here):
TimeC (bigint, unix timestamp) id (integer) value (integer).
The table currently has an index on
id. The table contains ~15mil rows. The three columns above all allow NULL values (I suspect that's a first clue here).
The query in question is like this:
select id, sum(value) as TotalV from table_name where timeC >=154013600000 and timeC < 1540742400000 group by id order by TotalV desc;
This query takes about 280sec on our server.
I decided to add an index on the date-related column
The query now takes about 260sec, i.e. the improvement is very small.
The "explain" utility shows, without the new index:
select_type: SIMPLE table: TableName type: index possible_keys: NULL key: TableName_CC_IDX key_len: 768 ref: NULL rows: 14830632 Extra: Using where; Using temporary; Using filesort
…and WITH the new index:
select_type: SIMPLE table: TableName type: range possible_keys: TableName_TimeC key: TableName_TimeC key_len: 9 ref: NULL rows: 540694 Extra: Using where; Using temporary; Using filesort
So my question is, why is it not using BOTH indexes in the latter case when both indexes exist and when both are relevant to the specific query? Generally, what could I do to speed up this query?
CREATE TABLE TableName ( id varchar(255) DEFAULT NULL, timeC bigint(20) DEFAULT NULL, value decimal(42,2) DEFAULT NULL, KEY TableName (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show indexes from TableName\G Table: TableName Non_unique: 1 Key_name: TableName_CC_IDX Seq_in_index: 1 Column_name: id Collation: A Cardinality: 29 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: