Originally Posted by shaunthomson
Didn't we have this discussion already.
Didn't we talk about "skipping" fields in multi-field indexes?
Didn't I even suggest that a good primary key would be
And didn't I note the reasons for putting the fields in that order in the (yes, three field) primary key?
CREATE TABLE djBooks (
PRIMARY KEY( djid, booknum, songid )
I even gave the examples:
-- a WHERE involving just DJID will use the index
-- a WHERE involving DJID and BOOKNUM will use the index
-- a WHERE involving all three fields will use the index
-- a WHERE involving just BOOKNUM will *NOT* use the index
-- a WHERE involving just SONGID will *NOT* use the index
-- a WHERE involving just BOOKNUM and SONGID will *NOT* use the index
-- a WHERE invovling just DJID and SONGID will use the index only for the DJID.
(All the above may not be true in other databases--SQL Server in particular--but it is a well document limitation of MySQL.)