CODEWITHSUNDEEP
mysqlquery-optimizationsql-execution-plan
Anyany Pan
Anyany Pan

Reputation: 777

Why index not working on non-unique datetime column?

Table struct:

CREATE TABLE `stat_old` (
  `dt` datetime NOT NULL,
  `offer_id` int(11) DEFAULT NULL,
  `aff_id` int(11) DEFAULT NULL,
  UNIQUE KEY `dt` (`dt`,`offer_id`,`aff_id`),
  KEY `dt_2` (`dt`),
  KEY `offer_id` (`offer_id`),
  KEY `aff_id` (`aff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

the dt field stores datetime values cast to hour like '2019-01-01 01:00:00', '2019-01-01 02:00:00', and its non unique.

query:

explain select *
FROM stat_old
WHERE
  dt between '2019-02-11 16:00:00' and '2019-02-18 15:59:59'
order by dt;

result:

+----+-------------+----------+------+---------------+------+---------+------+----------+-----------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows     | Extra                       |
+----+-------------+----------+------+---------------+------+---------+------+----------+-----------------------------+
|  1 | SIMPLE      | stat_old | ALL  | dt,dt_2       | NULL | NULL    | NULL | 18914072 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+----------+-----------------------------+

As you can see, it nearly scan the full table which has 20,044,835 rows. Actually the result data has only 2,108,707 rows. Why index on dt is not using ? How can I fix this ?

Upvotes: 0

Views: 376

Answers (1)

danblack
danblack

Reputation: 14691

Making dt the first part of the primary key will help when retrieving a larger date range in dt order:

alter table stat_old
drop key dt,
drop key dt_2,
add primary key (`dt`,`offer_id`,`aff_id`)

Upvotes: 2

Related Questions