mysql create table and set createtime(int) for index, but select not use, so why?

Question

mysql create table and set createtime(int) for index, but select not use, so why?

mysql> EXPLAIN SELECT
    -> player_id,
    -> COUNT(*) AS count_num,
    -> SUM( add_gold ) AS sum_add_gold
    -> FROM
    -> cloud_data_player_gold_log
    -> WHERE
    -> 1 = 1
    -> AND create_time >= 1561046400
    -> GROUP BY
    -> player_id
    -> ORDER BY
    -> sum_add_gold ASC
    -> LIMIT 0, 10;
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table                      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                        |
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | cloud_data_player_gold_log | NULL       | ALL  | create_time   | NULL | NULL    | NULL | 555659 |    44.47 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show index from cloud_data_player_gold_log;
+----------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| cloud_data_player_gold_log |          0 | PRIMARY     |            1 | log_id      | A         |      555659 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | channel_id  |            1 | channel_id  | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | game_id     |            1 | game_id     | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | game_id_2   |            1 | game_id     | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | game_id_2   |            2 | room_id     | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | create_time |            1 | create_time | A         |       15356 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.04 sec)

table total data line is 560000, the sql run time over 0.6s

Answer 1

MySQL assumes that a significant number of rows (44%) are after create_time >= 1561046400. If it would use an index, this would mean MySQL would have to read half of your table by jumping back and forth inside it. As data is stored in blocks (that consist of more than 1 row), it would actually read the table size several times over, since for every useful row it also reads several unneeded rows (although a lot of this is mitigated by caching).

In such a case, it's faster to just read the whole table from start to end once and throw the unneeded rows away, which MySQL has decided to do here.

You can prevent MySQL from having to read the actual table data by providing all the data it needs in the index, by having a covering index (create_time, player_id, add_gold). Then it can just read the index from create_time >= 1561046400 to end in one go without the time consuming jumping inside the table.

If MySQL estimated incorrectly, e.g. there might actually be only a handful of rows in your time range, or if you just want to test the execution time with the index, you can force MySQL to use it with e.g.

... FROM cloud_data_player_gold_log FORCE INDEX (create_time) WHERE ...

This has the general disadvantage that MySQL cannot adapt to changed data, different create_time-parameters or additional filters, e.g. if using a different index would be actually faster.

An alternative index you could try would be (player_id, create_time) (or, covering, (player_id, create_time, add_gold)), which supports the group by.

It will depend on your data distribution which one will be faster: an index starting with create_time has to read less rows, an index starting with player_id has to sort one less time. Depending on how many rows there are, one will offset the other.