Mysql Datetime Index

Question

Here is a table

+------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | datecolumn | datetime | YES | MUL | NULL | | +------------+----------+------+-----+---------+-------+ 

Data in the table

 +---------------------+ | datecolumn | +---------------------+ | 2007-01-01 00:00:00 | | 2007-01-03 00:00:00 | | 2008-01-03 00:00:00 | | 2009-01-03 00:00:00 | | 2010-01-01 00:00:00 | | 2010-01-02 00:00:00 | | 2010-01-03 00:00:00 | +---------------------+ EXPLAIN select * from test_date_index use key (datecolumn) where datecolumn>='2010-01-02 00:00:00'; +----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+ | 1 | SIMPLE | test_date_index | index | datecolumn | datecolumn | 9 | NULL | 7 | Using where; Using index | +----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) 

Why does mysql use 7 rows to restore 1 row?

Thanks in advance!

Answer 1

It uses an index.

This (most likely) does not use range access, since there are so few records in the table that finding the first record in a range is actually more expensive than just moving the index and filtering the values.

When you add more values ​​to the table, the access range is applied (since it is considered cheaper), and COUNT shows 1 , since you have only one record that satisfies the condition.