Incorrect work of optimiser

Asked by Ivan Sem

I have the next innodb table:

CREATE TABLE events (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  server int(11) UNSIGNED NOT NULL,
  internal_id bigint(20) UNSIGNED NOT NULL,
  pid int(11) UNSIGNED NOT NULL,
  event_datetime DATETIME NOT NULL,
  event_stamp int(11) NOT NULL,
  status tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  INDEX IDX_events (event_stamp, pid),
  INDEX IDX_events2 (event_stamp),
  UNIQUE INDEX UK_events_hash (internal_id, server)
)
ENGINE = INNODB;

The records count is ~ 5 millions. When I execute the next SQL:

EXPLAIN SELECT SQL_NO_CACHE id, internal_id, pid, status FROM events WHERE event_stamp BETWEEN UNIX_TIMESTAMP('2017-01-01') AND UNIX_TIMESTAMP(CURDATE());

Profiler say that there is 2 possible indexes, but no one used. Total execute time is 0.105ms. Then I add "FORCE INDEX (IDX_events2)" and profiler say that index used and total execute time is 0.02ms.

So why optimiser think that not using index and go through the ~2 mln records is faster than using index ? Executing with index more faster and it was logical.

Question information

Language:
English Edit question
Status:
Expired
For:
Percona Server moved to https://jira.percona.com/projects/PS Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Launchpad Janitor (janitor) said :
#1

This question was expired because it remained in the 'Open' state without activity for the last 15 days.