CODEWITHSUNDEEP
mysqlindexing
TryHarder
TryHarder

Reputation: 2777

Is column order important in mysql?

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   |
-----------------------------------------------

Upvotes: 3

Views: 1288

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300529

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).

Upvotes: 4

Related Questions