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

[Gregory.Spencer]

Hi there,

Using PHPMyAdmin and it is very usefully reporting problems with my
MySQL DB.
"PRIMARY and INDEX keys should not both be set for column
`column_name`"

and

"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.

[Gordon Burditt]

>Using PHPMyAdmin and it is very usefully reporting problems with my

MySQL DB.
"PRIMARY and INDEX keys should not both be set for column
`column_name `"

and

"More than one INDEX key was created for column `column_name`"
Show us the table definition, preferably SHOW CREATE TABLE output.
1) Will these warnings have a great impact on the speed of my DB?
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.
2) Can I expect a performance improvement if I do fix them?
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(.....),
value

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).
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.

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