Re: How to move partition files

[Robert Freeland]

We have a very similar application in which there are hundreds of gigs of data, but the most recent data is accessed most frequently. It might seem like a step backward, but after experimenting with partitions for many months, we ultimately decided to just use MERGE tables instead. The underlying tables are just ordinary MyISAM tables, so you can move them around, repair them, and query them independently. Better yet, you can group those tables together selectively into new MERGE tables on the fly, since a MERGE table is itself just a shell and therefore takes zero time to build.

The big problem with using a WHERE condition to trigger partition pruning is that this usually causes the MySQL optimizer not to use available indexes for other selection criteria in the query itself. If your data is laid out in a predictable manner (monthly, for instance), it's WAY faster to write your queries against specific tables and use the indexes there. (That's assuming you have other criteria in your queries.)



Edited 1 time(s). Last edit at 10/08/2008 10:47PM by Robert Freeland.