MySQL Forums :: InnoDB :: Range on primary key using secondary index.

Advanced Search

Range on primary key using secondary index.
Posted by: James French ()
Date: May 11, 2009 03:15PM

Hey folks can't seem to get InnoDB to include the primary key as part of its intervals when using a secondary index. (Using MySQL version 5.1.30)

DROP TABLE IF EXISTS `am`.`range_query_test`;
CREATE TABLE `am`.`range_query_test` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` int(10) unsigned NOT NULL,
KEY `value` (`value`)

At this point the table is populated with random values from 1-10 for "value" column.

EXPLAIN SELECT * FROM range_query_test r WHERE value = 3 AND ID > 500 ORDER BY ID ASC;

produces this result:
id: 1
select_type: SIMPLE
table: r
type: ref
possible_keys: PRIMARY,value
key: value
key_len: 4
ref: const
rows: 56
extra: Using where; Using index

An alter table is run to make "ID" the second column on the "value" index.

ALTER TABLE `am`.`range_query_test` DROP INDEX `value`,
ADD INDEX `value` USING BTREE(`value`, `ID`);

The same EXPLAIN is run, and the results look much better. Type is now "range", and "rows" is down to 6. Additionally, it now reports it is using 8 bytes of the key, (indicating the 'value' column and the 'ID' column)

The question is...why is this necessary? I was always under the impression that InnoDB always behaved as though the Primary Key was extended onto each secondary key. What is it about adding the PK explicitly to the end of that index changed the performance so drastically?

Options: ReplyQuote

Subject Views Written By Posted
Range on primary key using secondary index. 5140 James French 05/11/2009 03:15PM
Re: Range on primary key using secondary index. 1755 Aftab Khan 05/16/2009 09:41AM

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.