Search code examples
mysqlclustered-indexnon-clustered-index

MySQL: where clustered (logical indexes) save

Non-Clustered file stored in data files, Clustered index stores data in logical index order.

Where does logical order resides?

Solution

  • Most MySQL indexes are structured as BTrees. (See Wikipedia)

    (I am excluding FULLTEXT and SPATIAL indexes from this discussion.)

    InnoDB's PRIMARY KEY is a BTree and "clustered" with the data. That is, the data is stored in PK order in the BTree.

    InnoDB "secondary keys" and all MyISAM indexes are stored in separate BTree structures.

    In the leaf node of a MyISAM index is an offset into the data file of where the entire row is stored. In the leaf node of an InnoDB secondary index is a copy of the PRIMARY KEY column(s).

    All indexes are stored "ascending" in numerical (if some numeric column) or collation (if some string type) order.

    (I don't know what you mean by "logical", but maybe I answered it.)