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?
mysql> SHOW CREATE TABLE BillingLog;
| BillingLog |CREATE TABLE `BillingLog` (
`CallID` bigint(20) unsigned NOT NULL default '0',
`ChargeType` char(5) NOT NULL default '',
`DNIS` char(10) NOT NULL default '',
`Application` char(15) NOT NULL default '',
`Duration` int(11) NOT NULL default '0',
`VoiceSystem` char(8) NOT NULL default '',
`Mailbox` char(15) NOT NULL default '',
`PhoneNumber` char(20) NOT NULL default '',
`InfoDigs` char(2) NOT NULL default '',
`BillingRate` char(5) NOT NULL default '',
`TimeStamp` timestamp(14) NOT NULL,
`Sequence` bigint(20) NOT NULL auto_increment,
`Start` datetime NOT NULL default '0000-00-00 00:00:00',
`End` datetime NOT NULL default '0000-00-00 00:00:00',
`Provider` char(5) NOT NULL default '',
`ProviderCost` double(15,3) NOT NULL default '0.000',
`BillProvider` char(5) NOT NULL default '',
`CustomerPrice` double(15,3) NOT NULL default '0.000',
PRIMARY KEY (`Sequence`),
UNIQUE KEY `Sequence` (`Sequence`),
KEY `Start` (`Start`),
KEY `Application` (`Application`,`Start`),
KEY `CallID` (`CallID`)
) TYPE=MyISAM |
mysql> EXPLAIN SELECT DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'), Duration,
PhoneNumber, DNIS, Provider, Sequence, ProviderCost, CustomerPrice,
CallID FROM BillingLog WHERE Start BETWEEN '20041001000000' AND
'20041031000000';
+------------+------+---------------+------+---------+------+---------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------------+------+---------------+------+---------+------+---------+-------------+
| BillingLog | ALL | Start | NULL | NULL | NULL | 9391282 | Using
where |
+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT
-> DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'),
-> Duration,
-> PhoneNumber,
-> DNIS,
-> Provider,
-> Sequence,
-> ProviderCost,
-> CustomerPrice,
-> CallID
-> FROM
-> BillingLog
-> WHERE
-> Start BETWEEN '20041001000000' AND '20041031000000'
-> ;
+------------+------+---------------+------+---------+------+---------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------------+------+---------------+------+---------+------+---------+-------------+
| BillingLog | ALL | Start | NULL | NULL | NULL | 9391289 | Using
where |
+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT
-> DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'),
-> Duration,
-> PhoneNumber,
-> DNIS,
-> Provider,
-> Sequence,
-> ProviderCost,
-> CustomerPrice,
-> CallID
-> FROM
-> BillingLog
-> WHERE
-> Start BETWEEN '20041001000000' AND '20041031000000'
-> LIMIT 1000
-> ;
+------------+-------+---------------+-------+---------+------+---------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------------+-------+---------------+-------+---------+------+---------+-------------+
| BillingLog | range | Start | Start | 8 | NULL | 2061426 | Using
where |
+------------+-------+---------------+-------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT
-> DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'),
-> Duration,
-> PhoneNumber,
-> DNIS,
-> Provider,
-> Sequence,
-> ProviderCost,
-> CustomerPrice,
-> CallID
-> FROM
-> BillingLog
-> WHERE
-> Start BETWEEN '20041001000000' AND '20041031000000'
-> LIMIT 10000000
-> ;
+------------+------+---------------+------+---------+------+---------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------------+------+---------------+------+---------+------+---------+-------------+
| BillingLog | ALL | Start | NULL | NULL | NULL | 9391313 | Using
where |
+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT
-> DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'),
-> Duration,
-> PhoneNumber,
-> DNIS,
-> Provider,
-> Sequence,
-> ProviderCost,
-> CustomerPrice,
-> CallID
-> FROM
-> BillingLog
-> WHERE
-> Start BETWEEN '20041001000000' AND '20041031000000'
-> LIMIT 2000000
-> ;
+------------+-------+---------------+-------+---------+------+---------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------------+-------+---------------+-------+---------+------+---------+-------------+
| BillingLog | range | Start | Start | 8 | NULL | 2061431 | Using
where |
+------------+-------+---------------+-------+---------+------+---------+-------------+
1 row in set (0.05 sec)
Jeremiah Gowdy
Senior Software Engineer
FreedomVOICE Systems
