stumped on datetime index issue

MySQL & MariaDB Other MySQL® Questions
1

I can’t figure out why an datetime index is ignored by mysql if the greater than operator is used.

Here’s the table:

CREATE TABLE IF NOT EXISTS maverick_player_stints ( unique_id int(10) unsigned NOT NULL default ‘0’, player_id mediumint(9) NOT NULL default ‘0’, start_stint datetime NOT NULL default ‘0000-00-00 00:00:00’, end_stint datetime NOT NULL default ‘0000-00-00 00:00:00’, at_position tinyint(3) unsigned NOT NULL default ‘0’, on_team tinyint(3) unsigned NOT NULL default ‘0’, PRIMARY KEY (unique_id), KEY player_id~stint (player_id,start_stint), KEY stint_starts (start_stint)) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here’s the first query explained:

explain select * from maverick_player_stints where start_stint = ‘2007-01-22 09:00:00’;

±—±------------±-----------------------±-----±--------------±-------------±--------±------±-----±------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±-----------------------±-----±--------------±-------------±--------±------±-----±------+| 1 | SIMPLE | maverick_player_stints | ref | stint_starts | stint_starts | 8 | const | 2 | | ±—±------------±-----------------------±-----±--------------±-------------±--------±------±-----±------+

Here’s the same query with the greater than operator:

explain select * from maverick_player_stints where start_stint > ‘2007-01-22 09:00:00’;

±—±------------±-----------------------±-----±--------------±-----±--------±-----±------±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±-----------------------±-----±--------------±-----±--------±-----±------±------------+| 1 | SIMPLE | maverick_player_stints | ALL | stint_starts | NULL | NULL | NULL | 15401 | Using where | ±—±------------±-----------------------±-----±--------------±-----±--------±-----±------±------------+

Why is the possible key not used in the second case? It’s scanning the entire table. Is this something particular to the datetime data type?

I could use some help on this one, thanks.

2

Peter blogged that the EXPLAIN feature can be untrustworthy in certain situations.

http://www.mysqlperformanceblog.com/2006/07/24/mysql-explain -limits-and-errors/

I wouldn’t think this is one of those situations, but maybe it is. Can anyone explain what I have been seeing?

3

Looks like this forum is kind of dead. Peter himself hasn’t posted on it in months.

Just to conclude this thread, it does look like this is a limitation with the EXPLAIN statement.

I modified the query to make the result set smaller and mysql now indicates the key is being used.

EXPLAIN SELECT *
FROM maverick_player_stints
WHERE start_stint
BETWEEN ‘2007-01-22 09:00:00’
AND ‘2007-02-22 12:00:00’

±—±------------±-----------------------±------±--------------±-------------±--------±-----±-----±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±-----------------------±------±--------------±-------------±--------±-----±-----±------------+| 1 | SIMPLE | maverick_player_stints | range | stint_starts | stint_starts | 8 | NULL | 87 | Using where | ±—±------------±-----------------------±------±--------------±-------------±--------±-----±-----±------------+1 row in set (0.00 sec)

4

Second conclusion:

The cardinality of the start_stints column is very low, something like 3000 of 15000. Apparently mysql decides that it’s better to do a full table scan than try to retrieve an index when many rows have duplicates.

So the original cause of not using the key was probably the low cardinality of the indexed column.