how do you create mysql index? multi-column or one column per index? what's your thinking?
A:
This is something that depends heavily on the data stored in your tables. Is this a db with a single table with queries that are only run against the unique id/PK of the record? If so, then just index a single column, the id column. If not, it may call for indexing across more than one column.
It's a case by case issue that can't be generalized. Indexing can be very tricky and over indexing can actually hurt performance (especially on inserts and updates). Make extensive use of the "EXPLAIN" statement in mysql to see how your index changes affect the way mysql actually queries the data.
CRasco
2009-09-29 13:50:44