Range on primary key using secondary index.
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,
PRIMARY KEY (`ID`),
KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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?
Subject
Views
Written By
Posted
Range on primary key using secondary index.
6592
May 11, 2009 03:15PM
1991
May 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.