How does secondary index scanning work in InnoDB?

Question

InnoDB has two types of indexes: primary (clustered) and secondary (with the pimary index key).

When I print a query that scans fields indexed by a secondary index, my question begins. Does InnoDB check the secondary index and retrieve records one by one that fall into state? If I have 50 hits in a secondary index, is InnoDB looking for a 50x primary index?

Answer 1

Is InnoDB scanning a secondary index and retrieving records one by one that are in state?

If you select columns that are not covered by the secondary index, then yes, it should retrieve them from the table (cluster index).

If you have this layout:

CREATE TABLE a (id INT NOT NULL PRIMARY KEY, ca INT NOT NULL, cb INT NOT NULL, KEY(ca))

SELECT  cb
FROM    a
WHERE   ca = $some_value

the following happens:

• B-Tree seek, InnoDB ca, $some_value

• , , , $some_value.

• ca ( ) id ( ), InnoDB cb .

• id . id, B-Tree.

, :

SELECT  ca, id
FROM    a
WHERE   ca = $some_value

, 3 4 . using index.

50 , InnoDB 50- ?

( )