List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:April 7 2008 1:17am
Subject:Re: Indices in InnoDB/MySQL
View as plain text  
Hi Robert,

On Tue, Apr 1, 2008 at 1:01 PM, Robert DiFalco <rdifalco@stripped> 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/
Thread
Indices in InnoDB/MySQLRobert DiFalco1 Apr
  • Re: Indices in InnoDB/MySQLBaron Schwartz7 Apr
Re: Indices in InnoDB/MySQLWm Mussatto1 Apr
  • RE: Indices in InnoDB/MySQLRobert DiFalco1 Apr
Re: Indices in InnoDB/MySQLPaul DuBois1 Apr