CODEWITHSUNDEEP
mysqlpercona
Pierre
Pierre

Reputation: 521

MySQL not using index for BETWEEN query. What am I doing wrong?

I've tried different variations of this query but I just can't get it to use the timestamp index. What am I doing wrong?

Any help will be greatly appreciated :)

MariaDB [alienvault]> explain extended SELECT * FROM alienvault_siem.acid_event WHERE (timestamp BETWEEN '2012-10-09 11:20:17' AND '2012-10-10 03:20:17');
+------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|    1 | SIMPLE      | acid_event | ALL  | timestamp     | NULL | NULL    | NULL | 4481579 |    50.00 | Using where |
+------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Table structure:

CREATE TABLE IF NOT EXISTS `acid_event` (
  (...)
  `timestamp` datetime NOT NULL,
  (...)
  PRIMARY KEY (`id`),
  (...)
  KEY `timestamp` (`timestamp`),
  (...)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Software: Percona Server

Software version: 5.5.27-28.1 - Percona Server (GPL), Release 28.1

Upvotes: 1

Views: 1486

Answers (2)

Pomyk
Pomyk

Reputation: 3328

Could be caused by: - index is not selective enough and mysql thinks full scan will by faster - mysql has bad index statistics

You can check output of "SHOW INDEX FROM alienvault_siem.acid_event;" You can also try to force index use (but it might be bad in case if fullscan is faster):

SELECT * FROM alienvault_siem.acid_event FORCE INDEX (`timestamp`) WHERE (timestamp BETWEEN '2012-10-09 11:20:17' AND '2012-10-10 03:20:17');

Upvotes: 3

Jim Garrison
Jim Garrison

Reputation: 86774

The problem is that you provided the date literals as strings.

If you use the str_to_date() function as in 

WHERE timestamp 
  BETWEEN str_to_date('2012-10-09 11:20:17','%Y-%m-%d %H:%i:%s') 
      AND str_to_date('2012-10-10 03:20:17','%Y-%m-%d %H:%i:%s')

then MySQL will be able to use the index.

Upvotes: 1

Related Questions