Index's in MySQL - Multiple index warning from PHPMyAdmin - please help.

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi there,

Using PHPMyAdmin and it is very usefully reporting problems with my

"PRIMARY and INDEX keys should not both be set for column


"More than one INDEX key was created for column `column_name`"

1) Will these warnings have a great impact on the speed of my DB?
2) Can I expect a performance improvement if I do fix them?

This probably stems from a few holes in my knowledge of indexes. I
understand what they do in principle but still a bit confused about
best implementation practises and can't find a useful online resource
to explain.

3) Can anyone point out a good guide on MySQL indexes and more details
on these warnings - particular with regard to multipe column indexes
and when these are useful.

There is one area in my DB where perfomance is darn slow. I cannot
figure out why and hoping better index understanding will help.

Thanks in advance.

Re: Index's in MySQL - Multiple index warning from PHPMyAdmin - please help.

Quoted text here. Click to load it

Show us the table definition, preferably SHOW CREATE TABLE output.

Quoted text here. Click to load it

If you have two identical indexes on the same column (or set of
columns in the same order), it wastes disk space and it takes time
to keep them updated, to no benefit whatever.  As to whether it is
a GREAT impact, I don't know, but it will have most impact on
updates, not reads.  Getting rid of IDENTICAL indexes is a no-brainer.

Quoted text here. Click to load it

Probably but it may not be measurable.

Note that having the same column in two different indexes is
not necessarily bad, and it can improve performance.  Consider
the schema:

table hourly
    stamp    datetime,
    host    enum(.....),
    type    enum(.....),

This table holds a bunch of statistics:  stamp is which hour, host
is which host it's for, type is the particular statistic over
an hour (mail received, mail sent, bytes of network traffic, etc.).

I have a primary key of (stamp, host, type) for queries that look
at the completeness of the statistics (and keep out duplicates).
(select * from hourly order by stamp, host, type).  I have an index
on (type, host) for queries that generate graph data (select * from
hourly where type = 'sometype' and host = 'somehost' and stamp >=
'sometime' order by stamp ).  Neither index is much use for the other query.
If I remove either one, performance for the corresponding query slows
to a crawl (database has about 6 million records).

Quoted text here. Click to load it

Show us the query and the schema.  Note that a multi-column key
isn't good for much unless you know the values of the *FIRST* N
columns in it (much like looking for all of the people with the
first name "George" in a traditionally-indexed phone book is a
painful job) or perhaps the first N-1 columns and a range on
the Nth.

                        Gordon L. Burditt

Site Timeline