MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote

Written By
August 14, 2009 10:00AM
August 15, 2009 03:26PM
August 17, 2009 10:31AM
August 17, 2009 08:04PM
August 26, 2009 09:23AM
August 26, 2009 11:30AM
August 26, 2009 11:55AM
Re: DATETIME vs. INT(11)?
August 26, 2009 11:34PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.