innodb data structure

I believe I understand how the INNODB structures the table (by using clustered btree indexes=PK and the leafs containing the rows themselves). Secondary indexes using the same principle (btree clustered index=secondary index) and the leaf contains the PK that is used as a pointer(that is the reason why second index lookup may be need)开发者_开发技巧.

http://www.chenyajun.com/wp-content/uploads/2008/12/3-9.jpg so sorting is based on the index in INNODB.

But I really cannot understand how the clustering btree index principle is used to sort and store physically the covering/composite indexes in INNODB.

this article might prove of interest :)

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

It can be explained by the diagram you linked to:

• A (typical) secondary key index access first starts by traversing a non-clustered index, finding the value(s) of the primary key, and then traversing the primary key to locate the data. Or to put it another way: two independent tree lookups to find data.

• A covering index is where all data required is stored in the secondary key index. Typically you are just piggybacking one column into the back of the index - and that's the only column you request as part of your SELECT statement.