MySQL Forums
Forum List  »  Optimizer & Parser

Range query performance question
Posted by: Camelia Ticau
Date: February 27, 2006 01:52PM


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`)
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_len: 12
ref: NULL
rows: 11191
Extra: Using where; Using index
1 row in set (0.00 sec)


Options: ReplyQuote

Written By
Range query performance question
February 27, 2006 01:52PM
February 27, 2006 02:50PM

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.