Friday Dec 04, 2009

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

Tuesday Nov 24, 2009

Improved read-only load balancing with MySQL Proxy 0.7.x

A customer had come to us this week asking about some of the functionality in the ro-balance.lua script that ships with MySQL Proxy. As I investigated things further, I came to realise that due to some changes in the 0.7.x series, the script was broken.

Being the helpful fellow I am, I updated the ro-balance.lua script so that it works again. I also ended up making some nifty improvements...[Read More]

Tuesday Dec 16, 2008

FULLTEXT and Asian Languages with MySQL 5.0

I have seen a few people now ask about using MySQL's FULLTEXT indexing with asian languages such as Chinese, Japanese and Korean (herein referred to as CJK.), however, there doesn't seem to be a good centralised article that covers it.

The information is out there, I just don't think it has been well presented yet.

As I have recently done a bunch of research on this topic for a customer, I figured it might be a good opportunity to make my debut in the MySQL blogosphere.

[Read More]
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