What if you query based on two different indexed fields? MySQL tries to select the index that will result in the fewest rows being examined. So the results will vary depending on your data and the values you choose.
mysql> SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600 AND Id <=
5000000;
+----------+
| COUNT(*) |
+----------+
| 1175 |
+----------+
1 row in set (0.04 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM Headline
-> WHERE ExpireTime >= 1112201600 AND Id <= 5000000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: range
possible_keys: PRIMARY,ExpireTime
key: ExpireTime
key_len: 4
ref: NULL
rows: 12009
Extra: Using where
1 row in set (0.00 sec)
For this query, given the choice between the primary key field (Id) and the ExpireTime, MySQL decided to use ExpireTime. However, if the ExpireTime value is changed so that it matches many more rows, MySQL should favor the primary key:
mysql> EXPLAIN SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1012201600 AND Id <=
5000000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: range
possible_keys: PRIMARY,ExpireTime
key: PRIMARY
key_len: 4
ref: NULL
rows: 13174
Extra: Using where
1 row in set (0.00 sec)
As expected, it does.
Again, this decision-making process is all based on MySQL’s notion of what the data looks like—how evenly distributed the values are. Different storage engines (InnoDB, MyISAM, BDB) use different methods to gather those statistics. As a result, you may find that some queries are executed differently if you convert your data to a different table type. Of course, running ANALYZE TABLE will also affect MySQL’s statistics.
