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.
ALTER TABLE `documents` ADD INDEX (`offer_number`, `contract_number`);
ALTER TABLE `documents` ADD INDEX (`offer_number`); ALTER TABLE `documents` ADD INDEX (`contract_number`);
What’s the difference?
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.
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.