[EMAIL PROTECTED] wrote:
"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
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
