Hi Robert, On Tue, Apr 1, 2008 at 1:01 PM, Robert DiFalco <[EMAIL PROTECTED]> wrote: > I've been told that an index always contains the primary key. So if I
A non-primary key index, in InnoDB, contains the primary key values at the leaf nodes. However it does not contain them at the non-leaf nodes. The primary key values are appended to the secondary index's values. See here for more details: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html http://markmail.org/message/dmkafurf2urzlggl > have a LONG ID that is the primary key of table and create on index on > LONG VALUE, the index on LONG VALUE will actually work as a typical > compound index on ID,VALUE. My question is this, if I don't know that No, it will (sort of) work as a compound index on (VALUE, ID). > about MySQL and create my indices on purpose (i.e. not taking into > account implementation details about MySQL) I would create the index > explicitly on ID,VALUE. If I did that would I effectively have the same > index as one on just VALUE or would there some how be duplicate data in > the ID,VALUE index (i.e. ID, ID, VALUE)? You would have duplicated data in the index. If you created an index on (ID, VALUE) then each non-leaf node would contain (ID, VALUE) tuples. The leaf nodes would contain (ID, VALUE, ID) tuples. Whether you consider this duplicated depends on your point of view and what queries you're running. All of this is InnoDB-specific. It doesn't apply to MySQL in general. I drew some pretty pictures that may help illustrate the InnoDB index structures: http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/ Regards Baron -- Baron Schwartz, Senior Consultant, Percona Inc. Tel: +1 888 401 3401 ext 507 24/7 Emergency Line +1 888 401 3401 ext 911 Our Services: http://www.percona.com/services.html Our Blog: http://www.mysqlperformanceblog.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
