Incorrect work of optimiser
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(
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
- Assignee:
- No assignee Edit question
- Last query:
- Last reply: