I've been told that an index always contains the primary key. So if I
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
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)?

R.

On 4/1/08, Paul DuBois <paul@mysql.com> wrote:
>
> At 10:01 AM -0700 4/1/08, Robert DiFalco wrote:
> >I've been told that an index always contains the primary key.
>
>
> By who?
>
> Ask for proof.



I guess he was referring to the fact that InnoDB stores the primary key
values alongside the indexed columns
value, as a way of referencing the associated row.

--
Sincerely yours,
Olexandr Melnyk <><
http://omelnyk.net/

There is already a primary key index on ID.

-----Original Message-----
From: Wm Mussatto [mailto:mussatto@csz.com]
Sent: Tuesday, April 01, 2008 10:50 AM
To: mysql@lists.mysql.com
Subject: Re: Indices in InnoDB/MySQL

On Tue, April 1, 2008 10:01, Robert DiFalco wrote:
> I've been told that an index always contains the primary key. So if I
> 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
> 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)?
>
> R.
>
Close but not quite there...
You should always have AN index which is the primary key. You can
create other indexes which don't. If you are searching for 'Value' then
that should be the index. If you do ID,VALUE it can't use the index
AFAIK because it won't know the ID.
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=r...o@tripwire.com

Right, the proof is that if I have an PKEY on ID and an index just on
VALUE in MySQL then a query that would use both ID and VALUE works fine
with just the index on VALUE.

For Oracle, I need an explicit compound index (in addition to the PKEY)
on (ID,VALUE).

The results on MySQL get a little blurry when the PKEY is compound.

-----Original Message-----
From: Olexandr Melnyk [mailtomelnyk@gmail.com]
Sent: Tuesday, April 01, 2008 11:08 AM
To: mysql@lists.mysql.com
Subject: Indices in InnoDB/MySQL

On 4/1/08, Paul DuBois <paul@mysql.com> wrote:
>
> At 10:01 AM -0700 4/1/08, Robert DiFalco wrote:
> >I've been told that an index always contains the primary key.
>
>
> By who?
>
> Ask for proof.



I guess he was referring to the fact that InnoDB stores the primary key
values alongside the indexed columns value, as a way of referencing the
associated row.

--
Sincerely yours,
Olexandr Melnyk <><
http://omelnyk.net/

Hi !

Robert DiFalco wrote:
> Right, the proof is that if I have an PKEY on ID and an index just on
> VALUE in MySQL then a query that would use both ID and VALUE works fine
> with just the index on VALUE.

An index is a means to speed up access to the data when the most
identifying thing, the primary key, isn't known.
Without an index, you would have to scan ("sequential search") all the
data for the qualifying row; but with a suitable index you have some
kind of reference from the index entry to the row of data.

The concept of an index does not specify what exactly this reference is.
With InnoDB, it is the primary key AFAIK.

Depending on the optimizer, this then *may* allow for "index-only"
strategies: If all fields you need are part of either the index list
(VALUE) or the primary key (ID), then there is no need to access the
"real" data once the index entry is available.

>
> For Oracle, I need an explicit compound index (in addition to the PKEY)
> on (ID,VALUE).

AFAIK, Oracle uses some physical address to access the record, so the
index on VALUE does not contain the ID fields.
This also means you cannot use an index-only strategy if you need a part
of ID which is not contained in VALUE.

Using physical addresses forces the system to update all index entries
if a record location changed (say, because a field of variable length
was updated and the record doesn't fit in its current location any
longer, or because the data pages get compacted after deletions).


HTH,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Hi Robert,

On Tue, Apr 1, 2008 at 1:01 PM, Robert DiFalco <rdifalco@tripwire.com> 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/...al-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 illustrate the InnoDB index
structures:
http://www.xaprb.com/blog/2006/07/04...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/