MySQL - Many column in an index? Or one by one?

Suppose that a product can have up to five (5) complaints. In this case, there is a table with five (5) columns. These five columns directly involved in the other table, where reports claim codes.

See in: http://img263.imageshack.us/img263/4990/01mysql.jpg

From this moment, I have to make a relationship between two tables. So which of the following is more correct to create indexes:

a) creating index one by one?

ALTER TABLE `complaint` ADD INDEX (`code_01`) 
ALTER TABLE `c开发者_如何学运维omplaint` ADD INDEX (`code_02`) 
ALTER TABLE `complaint` ADD INDEX (`code_03`) 
ALTER TABLE `complaint` ADD INDEX (`code_04`) 
ALTER TABLE `complaint` ADD INDEX (`code_05`) 

b) That all fields in a single index?

ALTER TABLE `complaint` ADD INDEX (`code_01`, 
                                   `code_02`, 
                                   `code_03`, 
                                   `code_04`, 
                                   `code_05`)

I believe the best option is a), because if I choose option b) and by accessing "Relation View" in phpMyAdmin, it shows that there are other fields saying that there is "No index defined!" in columns code_02, code_03, and code_04 code_05.

---

ATTENTION - FOR INSERT AND UPDATE:

If I use option b) and enter a code on record with columns code_02, code_03, and code_04 code_05, MySQL does not advise any errors that the code entered does not exist. Only returns a success message which should really be incorrect. So in this particular case, I have to use option a), right?

---

When you have a index on multiple columns, the index will only be used when you are accessing by the left most columns.

Option a will create 5 separate indexes, Option B will create just 1

if you will be searching or sorting by any of the columns, then you must use option A However, if you will be searching by the columns in the order of the index, then use option B

Example

SELECT ID
FROM table
WHERE code_03 = 'asd' OR code_02 = 'asds'

use option A

SELECT ID
FROM table
WHERE code_01 = 'asd' AND code_02 = 'asds'

use option B

If you can, use option B, as it will yield much better performance, since it can use a single index to search multiple columns. and the index will be smaller.

---

It depends on your queries!

Please read this:

http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/index-combine-performance

You might need to read more from that page.

---

If you can, normalize your design. Get those 5 code columns out of the Complaint table and create a junction table between Complaints and code_complaint to properly handle the many-to-many relationship.

As a general rule, any time you start numbering column names (code_01, code_02...), that should be a red flag that your design needs further normalization.

---

Ignoring the fact that many would expect this to be a Many-to-Many table, I'm assuming you're going to JOIN back to a complaint record on each of the fields e.g

Products
inner join product_complaint pc
ON p.pid = pc.pid
LEFT JOIN complaints c1
ON pc.code_01 = c1.c_id
LEFT JOIN complaints c2
ON pc.code_02 = c2.c_id
LEFT JOIN complaints c3
ON pc.code_03 = c3.c_id
LEFT JOIN complaints c4
ON pc.code_04 = c4.c_id
LEFT JOIN complaints c5
ON pc.code_05 = c5.c_id

In this case use option 1.

But the two options are mutually exclusive so you could do option 2 as well if determine you need it