Range query performance question

[Camelia Ticau]

Hi,

My question is raleted to range query.
Please see the table description and the explain capture below.

For this query, the explain tells me it has to parse 11191 records.

In my opinion, this is a bug:

Why does MySQL need to parse 11191 records, if the records are
sorted by (recordtype, recordnumber)? Logically, locating
(recordtype=0, recordnumber=100) should be O(logn) for a binary search.
If I would do the search manually, I would first locate the record for
(recordtype=0, recordnumber=100), then will will take the next record.

Why is MySQL searching for (recordtype=0, recordtype>100) so different?


mysql> show create table oaa\G
*************************** 1. row ***************************
Table: oaa
Create Table: CREATE TABLE `oaa` (
`recordnumber` bigint(20) NOT NULL default '0',
`recordtype` int(11) NOT NULL default '0',
`oaarecord` blob,
PRIMARY KEY (`recordtype`,`recordnumber`)
) TYPE=InnoDB MAX_ROWS=10000000 AVG_ROW_LENGTH=8210
1 row in set (0.00 sec)

mysql> select count(*) from oaa;
+----------+
| count(*) |
+----------+
| 21104 |
+----------+
1 row in set (16.77 sec)

mysql> explain select recordnumber from oaa where recordtype=0 and recordnumber>100 LIMIT 1\G
*************************** 1. row ***************************
table: oaa
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: NULL
rows: 11191
Extra: Using where; Using index
1 row in set (0.00 sec)

Thanks,
Camelia