phpMyAdmin complaining about indexes - confused

bunner_bob

I have a table on which I have created a number of indexes, based on common queries to that table.

The primary key (also unique) is ProductID. The primary index for the table is therefore just the primary key - ProductID

I also have two indexes for common queries:

ProductID
ClassID
IsCourse
Hidden

ProductID
Status
Hidden

The fields in these indexes are the fields used in "WHERE" clauses in common SQL queries.

Well, phpMyAdmin is telling me (in red letters, no less):

PRIMARY and INDEX keys should not both be set for column 'ProductID'

and

More than one INDEX key was created for column 'ProductID'

But, since I'm using ProductID in these queries it should be in the indexes, right? Or...well if that's wrong I'm not sure what I'm supposed to do.

The manual didn't help me here - or I just never found it.

NogDog

The primary key is an index; you do not need to assign an index to a primary key column. (Think of "primary key" as a special subset of "index".)

bunner_bob

So will MySQL use multiple indexes if I leave ProductID out of my two additional indexes? Will it use my index plus the primary key?

NogDog

Yes. To repeat, a primary key is an index. As far as query optimization goes, it's the same as assigning an index of type "unique" to that column.

bunner_bob

OK, thanks

But there's still some confusion. I'm working on another table now. Its primary key is UserID. I made an index on UserID and UserType (both parts of a WHERE clause - or rather, one part of a WHERE, other part of a JOIN condition):

SELECT count(*) FROM Users LEFT JOIN Membership ON Membership.UserID=Users.UserID WHERE Users.UserType='User';

When this index contains both these fields, EXPLAIN tells me it's using that key (and the Extra column says "using where, using index"). However, when I remove UserID from the index (so it's just UserType) EXPLAIN says it's not using any key (though it does list my index as a possible index) and the Extra column just says "using where".