Re: DATETIME vs. INT(11)?
Posted by:
Rick James
Date: August 26, 2009 11:34PM
When searching for one row on an indexed column, it will do a BTree (not binary) search to find it.
When a SELECT references only field(s) that exist in one index (especially a 'compound' index), and regardless of whether it wants one row or many, it will consider doing an "index scan".
The data is kept one place; the indexes are kept another.
A "table scan" is when it reads the data only. An "index scan" is when it picks one index and reads just it.
Usually, a row of data is dozens or hundreds of bytes wide. Usually an index row is much narrower. So, reading all of one index is likely to be faster than reading all the data, hence the optimization (indicated by "Using index" in EXPLAIN).
Your sample has 3 rows, so it pretty much does not matter how the query is executed. Even with 3K rows, performance diffs will be minor. When there are 3 million rows, it makes a big difference.
Also, the 'rows' in an index are in a particular order. If that order agrees with the GROUP BY or ORDER BY clause, it might be possible to skip the "filesort" that is implied by such clauses.
Your query has no WHERE clause, so it has to do a full scan -- either table or index, as it sees fit.
If you had a "range", like "WHERE x BETWEEN 123 AND 987", it would probably to try to do an index scan, but not scan the entire index. Instead, it would be smart enough to do a BTree search for 123, then "scan" thru the index until 987.