MySQL Forums
Forum List  »  Newbie

Re: indexing
Posted by: Rick James
Date: January 11, 2012 10:18PM

No.

The PRIMARY KEY in InnoDB only is stored with the data. The data, therefore, is in primary key order. It is organized in a BTree.

Every other index in InnoDB, and every index in MyISAM, is a BTree that is separate from the data.

The order of the data in MyISAM is unpredictable. (OK, there are simple examples where you can predict them.

InnoDB secondary keys (every index but the PRIMARY KEY) has the columns of the PK in the leaf nodes.

MyISAM index leaf nodes contain either the row number (for "fixed" sized records) or the offset into the data file (.MYD) (for "dynamic").

I have heard of someone creating an Engine with multiple clustered indexes. However, the implementation was to effectively have multiple copies of _all_ columns, each copy being sorted according to its "clustered index".

If you would like to discuss performance and/or workarounds, please describe your schema and desires further.

Options: ReplyQuote


Subject
Written By
Posted
January 10, 2012 12:01AM
January 10, 2012 12:43AM
January 10, 2012 03:05AM
Re: indexing
January 11, 2012 10:18PM
January 12, 2012 12:37AM


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.