The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

MySQL optimization with indexes

Can anyone help me understand the benefits and drawbacks to having multiple fields/columns of a table added to the Primary index?
Also, perhaps someone can shed some light on how such a primary index is handled by MySQL.
For instance, if I set up a Primary index on a table that contains 6 fields/columns that had all 6 fields/columns in the primary index. Would this give any benefit at all?
The reason for the questions are because I am walking into a situation where such a case exists. I don't know that there is any benefit to doing Primary indexes this way, but can someone shed light on the why's?

For instance, if I set up a Primary index on a table that contains 6 fields/columns that had all 6 fields/columns in the primary index. Would this give any benefit at all?

yes, a very clear benefit -- two, actually

first, it means that the database will automatically prevent you from entering duplicate values for all 6 columns, i.e. all 6 values are considered when deciding if it's a duplicate set of values

this is known as entity integrity

secondly, a primary key allows you to reference it from a foreign key, which means that in a child table, you can be assured that the foresign key value (again, the combination of all 6 values) must actually exist in the primary key

Ok, I see some clear benefits to doing this in terms of ensuring uniqueness. However, how does this translate into speed? Will having such a large primary index hurt significantly on inserting? And, will doing the index in this way truly help SELECT statements over setting up a primary index and many secondary indexes?

no, having a 6-column primary key does not significantly hurt inserting -- because if, for example, you used an auto_increment instead, you would still want to declare a unique key on the 6 columns as well (otherwise you're opening yourself up to duplicates, and "how do i remove all but one of my duplicate rows" has gotta be one of the most common pleas for help from people just using auto_increments)

whether the primary key is of any use in optimizing SELECTs depends on what the SELECTs are asking for

I have one more question that perhaps you can be of help with. I see one case of a primary index having 3 columns in it, then two of the columns are also set up as their own secondary indexes. Is there any benefit to this, or should I think of cleaning this up in some way?

Thanks, as I was a bit confused on these items. It just seemed to me that if there was a Primary index of 6 columns, then the SELECT would scan through the full index of 6 columns when it may only need to scan one of those columns.

Would you recommend creating secondary indexes in this case, or just sticking with the Primary? (Barring the knowledge of the frequency of this type of query.)