Why is MariaDB 10 refusing to use my index on a date field?

Posted on

Question :

I have the following table definition:

CREATE TABLE `bets` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_mp` bigint(20) NOT NULL DEFAULT '0',
  `coin` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `game_id` tinyint(4) NOT NULL DEFAULT '1',
  `group_turn_id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `direction` tinyint(1) NOT NULL DEFAULT '0',
  `bet_amount` double NOT NULL,
  `multiplier` double NOT NULL,
  `target` double NOT NULL DEFAULT '0',
  `result` double NOT NULL,
  `profit` double NOT NULL,
  `win` tinyint(1) NOT NULL,
  `seed` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `bets_time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=1189677 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You’ll note that there is a primary key as well as a secondary index on the ‘time’ field. Mysteriously though, the following query refuses to use the bets_time index:

EXPLAIN SELECT COUNT(1) AS num_bets, SUM(bet_amount) AS total_wager FROM bets WHERE `time` > (NOW() - INTERVAL 24 HOUR)

yields the following:

+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | bets  | ALL  | bets_time     | NULL | NULL    | NULL | 1132313 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+

As you can see it sees bet_time as a possible index, but does not use it. Any ideas as to why this might be happening? I guess I could use FORCE INDEX on the query, but I’d rather avoid hardcoding such query logic into the SQL and prefer an ‘proper’ solution.

Thanks a lot for any ideas.

Answer :

The optimizer seems to think that your index is not selective enough. I think that you can do one or more of the following things:

  • ANALYZE TABLE bets;
  • ALTER TABLE bets ADD INDEX bets_time_amount (time, amount); /* this is a covering index */
  • add a FORCE INDEX to your SELECT

Leave a Reply

Your email address will not be published. Required fields are marked *