MySQL Forums
Forum List  »  Optimizer & Parser

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

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Range query performance question
6371
February 27, 2006 01:52PM
2516
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.