Can anyone tell me why this makes sense? I have a SELECT which uses an indexed datetime field called Start with a BETWEEN range. If I select on this with no LIMIT, it does a full scan of the 9391282 records in the DB (key=NULL). However, if I do a limit of any value LESS than the number of records in the DB, it uses the index (key=Start). If I do a LIMIT of any value greater than the number of records in the DB it again doesn't use the index (key=NULL).
This is on MySQL 4.0.18. Am I missing a bug fix in a more recent version of MySQL?
from the documentation -->
Each table index is queried, and the best index is used unless the optimizer believes that it will be more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table. Now the optimizer is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size, so a fixed percentage no longer determines the choice between using an index or a scan.
-- Philippe Poelvoorde COS Trading Ltd.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
