phpMyAdmin wants to add multiple indices as one multi-column index

Question

Hey,

I'm creating tables using phpMyAdmin and want to define two different columns as indices. I'm not trying to create a multi-column index but phpMyAdmin creates them as such. Are there any possible issues with that? The fields don't relate to each other directly and both fields will not be used in WHERE clauses simultaneously.

Consider:

ALTER TABLE `documents` ADD INDEX (`offer_number`, `contract_number`);

And:

ALTER TABLE `documents` ADD INDEX (`offer_number`);
ALTER TABLE `documents` ADD INDEX (`contract_number`);

What's the difference?

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Given what you say about these fields, they should not be a part of one multi column index.

If you want to create single column indexes on PhpMyAdmin, you need to create them one at a time.

Answer 2

MySQL can only make use of an index if the first column(s) of the index match the columns used in the query. In other words, if you perform a query where an index on contract_number could be used, the composite index won't be used since contract_number is not the first column in that key. The composite index could be used for a query where offer_number is used, however.