SGreen@stripped wrote:
>"Michael J. Pawlowsky" <mikep@stripped> 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
>
>
Thanks for the answer.
I know it is an index... I did not know that an index using 2 columns
will be used when calling a statement that uses only one of those columns.
Just for the fun of it I will trace a query to see if uses it.
Thanks again,
Mike
