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
精彩评论