MySQL Forums
Forum List  »  InnoDB

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

Written By
Range on primary key using secondary index.
May 11, 2009 03:15PM

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.