Saturday, February 23, 2008

Order and guarantees

The Certified MySQL Associate exam is the entry level exam for those new to MySQL and it covers a lot of the basics. Sometimes the basics are so basic that they are not obvious.

Suppose you have a table with dozens of columns that has been in production for a period of time and you issue the statement 'SELECT * FROM long_used_table;'. What order will the records be returned from the server?

Well, it depends. And a second try with the same statement may give you different results. The rule is that there is not a guaranteed order. If you want the data in a specific order you need to add ORDER BY, i.e. 'SELECT * FROM long_used_table ORDER BY special_index;'.

Now, on the CMA exam you are ask about this guaranteed order (or lack there of) in a very explicit fashion. In the other exams, it is not as straight forward and you have better have that internalized when reading the question. And in 'real life', this will catch you every time it can!

No comments: