Mysql – thesql v5.5 index performance


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 TimeC.

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?

(from Comment)

    id varchar(255) DEFAULT NULL, 
    timeC bigint(20) DEFAULT NULL, 
    value decimal(42,2) DEFAULT NULL, 
    KEY TableName (id)

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:

Best Answer

You need a multicolumn index (id, timeC, value) or (timeC, id, value) because existing timeC and defailt id indexes are not sufficient.

The common rule is to create the index containing all the columns used by JOIN, GROUP BY, ORDER BY and WHERE clauses. The order columns should be listed in the index can vary due to the exact queries, columns cardinality and even the amount of data.