I have a simple message table with two indexes:
mysql> show keys from Message;
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Message | 0 | PRIMARY | 1 | id | A | 5643295 | NULL | NULL | | BTREE | |
| Message | 1 | timestamp | 1 | startTimestamp | A | 5643295 | NULL | NULL | | BTREE | |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
When an order is issued upon request, a very large number of lines are checked:
mysql> explain SELECT * from Message ORDER BY startTimestamp LIMIT 0,20;
+----+-------------+---------+-------+---------------+-----------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+-----------+---------+------+---------+-------+
| 1 | SIMPLE | Message | index | NULL | timestamp | 8 | NULL | 5643592 | |
+----+-------------+---------+-------+---------------+-----------+---------+------+---------+-------+
Total number of rows:
mysql> select count(*) from Message;
+----------+
| count(*) |
+----------+
| 5837363 |
+----------+
This query affects 96.7% of the rows. The index is equal BTREE, so as far as I know, it should just pull out the top 20 rows and return them. Be that as it may, it uses an index to access almost all the rows of the table, which is supposedly slower than a full table scan.
Am I mistaken in my assumption that it should just select the top 20 rows using an index and return them?
MySQL server version 5.0.45, and table type InnoDB.