Things you manage to miss - ALTER TABLE .. ORDER BY

It's funny how you can use a product heavily for so many years and still be completely unaware of some features.

Today a customer mentioned reordering a MyISAM table to get rid of fragmentation. I asked them to clarify what they meant by reordering, assuming that they were running an OPTIMIZE or a NULL ALTER TABLE operation and they said that they use ALTER TABLT t1 ORDER BY f1, f2, f3,..etc;

To my surprise checking out the syntax reference on the ALTER TABLE manual page, there it was in black and white. You can make the server rebuild your table and order the rows by your own sort criteria. This could be helpful in situations where you commonly read rows in a certain sort order because it will allow sequential disk reads rather than random seeks which can be much more expensive. Also, as a side effect of rebuilding the table you will do away with any fragmentation that exists.

Note: This is really only relevant for MEMORY or MyISAM tables. InnoDB tables will always be ordered by PRIMARY KEY. Also, the table will not be maintained in the order you specify, so changes to the table can cause your precious ordering to be messed up -- it might be more suitable for tables with somewhat static data.

In reality this is the sort of thing you'd really need to benchmark to see if it equates to any real performance increase for your application, but it is at least something to keep up your sleeve.

It just goes to show, you never stop learning!...

Comments:

You're welcome :)

Posted by Jeremy Cole on December 04, 2009 at 05:31 AM EST #

MySQL is not the only free RDMS that supports such a feature:
http://www.postgresql.org/docs/8.4/static/sql-cluster.html

Posted by Rob Wultsch on December 04, 2009 at 05:53 AM EST #

Hi Lachlan
I'm pretty sure ALTER TABLE ... ORDER BY was covered in the original training week you did with me as trainer. But that was a long time ago ;-)

Posted by Arjen Lentz on December 06, 2009 at 01:42 PM EST #

Before they introduced that support in ALTER TABLE, you used to have to do this with a myisamchk. So yeah thanks, Jeremy. :)

Posted by Sandi on December 11, 2009 at 08:11 PM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Lachlan Mulcahy

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today