I have an interesting question on composite keys. Say for instance I have a simple two column table consisting of customer_id and color. A customer can have multiple colors, but a customer_id and specific color must be unique. So customer 1, can have a blue and a red, but not two blues right. Pretty simple. So I create a composite key consisting of customer_id and color. Does MySQL automatically treat each one of these columns as a unique index? Or do I need to create indexes for each column as well (for faster selects). Hopefully that makes sense.
You need to specify a unique key using both columns. You can use the UNIQUE syntax with a single column or a composite key. You would use CREATE UNIQUE INDEX `customer_color`ON my_table (customer_id, color); The index would be used any time you are selecting by customer_id or customer_id and color. You would not be using the index if you needed to select by just color. It would also prevent duplicate customer_id/color combinations from being inserted, but would not prevent multiple entries for either customer_id or color.