•  

Announcement

Announcement Module
Collapse
No announcement yet.

stumped on datetime index issue

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • stumped on datetime index issue

    #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.
    Tags: None

  • #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?

    Comment


    • #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)

      Comment


      • #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.

        Comment

        Working...
        X