Issue with innodb clustered secondary index and range queries?

An InnoDB secondary index leaf node includes the primary key values, but if you want to do a range query on the ID value, then it needs the non-leaf nodes of the index to include the primary key values If you only select the ID in your select-list, then then it's redundant to add the primary key to the index definition. For example: CREATE TABLE Favorite ( id INT AUTO_INCREMENT PRIMARY KEY, something INT, KEY s (something), KEY s_with_id (something, ID) ) Either index would make the following query an index-only query. InnoDB prefers the more compact index s It can still be an index-only query because the leaf nodes of the index provide the ID value mysql> EXPLAIN SELECT something, ID FROM Favorite WHERE something = 1\G *************************** 1.

Row *************************** id: 1 select_type: SIMPLE table: Favorite type: ref possible_keys: s,s_with_id key: s key_len: 5 ref: const rows: 48 Extra: Using where; Using index But in the case when you also have an inequality or range condition on ID, it would get more benefit from an index that includes ID values in the non-leaf nodes as well. It can take advantage of the fact that ID values are sorted in the B-tree mysql> EXPLAIN SELECT something, ID FROM Favorite WHERE something = 1 and id A clustered index alters the storage of table data to match the order of the index. InnoDB primary keys are always a clustered index, in that the row of data is stored in the leaf node of the primary key index Re your comment: Keep in mind that a "range" query against the primary index can be superior to a "ref" query against a secondary index When your query uses a secondary index, it basically has to make two tree traversals per row: first to search the secondary index to get to a leaf node where it finds the primary key value, then second to use that primary key value to search the primary (clustered) index to get the rest of the columns It could be less expensive overall for your query to do a range query against the primary index, so it finds a small enough subset of rows and then applies your other conditions to the columns it finds.

It isn't using the secondary index, but it's still a win because it only had to do one tree traversal per row I say "could be" not to use weasel words, but because the better choice really depends on how many rows are matched by either condition. Usually the optimizer is pretty good at making this evaluation, so it's unnecessary to use FORCE INDEX to override its behavior.

An InnoDB secondary index leaf node includes the primary key values, but if you want to do a range query on the ID value, then it needs the non-leaf nodes of the index to include the primary key values. If you only select the ID in your select-list, then then it's redundant to add the primary key to the index definition. For example: CREATE TABLE Favorite ( id INT AUTO_INCREMENT PRIMARY KEY, something INT, KEY s (something), KEY s_with_id (something, ID) ); Either index would make the following query an index-only query.

InnoDB prefers the more compact index s. It can still be an index-only query because the leaf nodes of the index provide the ID value. Mysql> EXPLAIN SELECT something, ID FROM Favorite WHERE something = 1\G *************************** 1.

Row *************************** id: 1 select_type: SIMPLE table: Favorite type: ref possible_keys: s,s_with_id key: s key_len: 5 ref: const rows: 48 Extra: Using where; Using index But in the case when you also have an inequality or range condition on ID, it would get more benefit from an index that includes ID values in the non-leaf nodes as well. It can take advantage of the fact that ID values are sorted in the B-tree. Mysql> EXPLAIN SELECT something, ID FROM Favorite WHERE something = 1 and id Row *************************** id: 1 select_type: SIMPLE table: Favorite type: ref possible_keys: PRIMARY,s,s_with_id key: s_with_id key_len: 9 ref: const rows: 1 Extra: Using where; Using index PS: Please don't use the term "clustered" when describing a compound index, because clustered means something different with respect to indexes.

A clustered index alters the storage of table data to match the order of the index. InnoDB primary keys are always a clustered index, in that the row of data is stored in the leaf node of the primary key index.Re your comment: Keep in mind that a "range" query against the primary index can be superior to a "ref" query against a secondary index. When your query uses a secondary index, it basically has to make two tree traversals per row: first to search the secondary index to get to a leaf node where it finds the primary key value, then second to use that primary key value to search the primary (clustered) index to get the rest of the columns.

It could be less expensive overall for your query to do a range query against the primary index, so it finds a small enough subset of rows and then applies your other conditions to the columns it finds.It isn't using the secondary index, but it's still a win because it only had to do one tree traversal per row. I say "could be" not to use weasel words, but because the better choice really depends on how many rows are matched by either condition. Usually the optimizer is pretty good at making this evaluation, so it's unnecessary to use FORCE INDEX to override its behavior.

I've noticed that when I do FORCE INDEX (idx_faver_idx_id) then the query uses the index correctly and does seems to execute very quickly (also type is "ref" not "range"). You obviously know your stuff, so I don't doubt you, just wanted to see if this observation meant anything to you. And thanks for clarifying what "clustered" means.

– makeee Nov 19 at 1:09.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.