"Michael J. Pawlowsky" <[EMAIL PROTECTED]> wrote on 10/28/2005 11:28:42 AM:
> > When creating an index in phpMySQL I get a warning message about having > 2 indexes using the same field. > > The table is simply a product comment table. > > The first index is simply an INDEX on the product_id to speed up > displaying them when someone wants to lookup the comments for that product. > The second is a UNIQUE constraint on the product_id and the user_id to > make sure a user does not insert more than one comment per product. > > phpMySQL shows me the following warning: > *UNIQUE and INDEX keys should not both be set for column `product_id` > > *If that really is bad to do... besides programatically how would I do > this. > > > Thanks, > Mike > A UNIQUE *is* an INDEX with an added twist. It's a special kind of INDEX that only allows single combinations of the values in the columns that compose the INDEX. A regular INDEX does not have this restriction. Defining UNIQUE(product_id, user_id) creates an INDEX. Any index that uses more than one column also acts as an index for each left-most sets of columns, including just the first one. Your UNIQUE and your INDEX overlap and the INDEX is not necessary. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
