Is column order important in mysql?
indexing, mysql
Solution
Column order is only important when defining indexes, as this affects whether an index is suitable to use in executing a query. (This is true of all RBDMS's, not just MySQL)
e.g.
Index defined on columns `MyIndex(a, b, c)` in that order.
A query such as
select a from mytable
where c = somevalue
probably won't use that index to execute the query (depends on several factors such as row count, column selectivity etc)
Whereas, it will most likely choose to use an index defined as `MyIndex2(c,a,b)`
Update: see use-the-index-luke.com (thanks Greg).
Problem
I read somewhere that column order in mysql is important. I believe they were referring to the indexed columns. QUESTION: If column order is important, when and why is it important? The reason I ask is because I have a table in mysql similar to the one below. The primary index is on the left and I have an index on the far right. Is this bad? It is a MyISAM table and will be used predominantly for selects (no inserts, deletes or updates). ``` ----------------------------------------------- | Primary index | data1| data2 | d3| Index | ----------------------------------------------- | 1 | A | cat | 1 | A | | 2 | B | toads | 3 | A | | 3 | A | yabby | 7 | B | | 4 | B | rabbits | 1 | B | ----------------------------------------------- ```