|Showing entries 1 to 6|
Giuseppe Maxia’s new article at the MySQL DevZone rightly points out that using partitions doesn’t work by magic; as with any other enhancement, it requires a bit of know-how to realise its value. MySQL 5.1 Partitions in Practice reviews some of the basics and points out a few gotchas (if you want to partition on a DATE or DATETIME column, pay close attention here). He then provides some realistic examples showing how you actually get partition pruning working for your queries and backs it up with some interesting performance figures.
On a related note, I recently reorganised the MySQL Manual’s discussion of[Read more...]
We’ve been getting lots of questions about how MySQL 5.1 optimises queries on partitioned tables. Recently, we added some new information about this to our Internals Manual provided by one of our developers, Sergey Petrunia. (I hope that my edits and formatting haven’t messed up anything — if there are any problems with what’s been published in the Internals Manual, then the fault’s probably mine and not his.)
The new section can be accessed here, and includes coverage of the following topics:
ONclauses into intervals
I recently discovered that I’d made a huge blunder in the Partitioning Limitations section of the MySQL 5.1 Manual. I’d had the idea that unique keys don’t effect your choice of partitioning columns unless there’s no primary key.
My latest revision of the documentation for this limitation isn’t much better.
Then, as I was sitting here just now trying to persuade my daughter that she really ought to have something for lunch besides marshmallows, it came to me.
It’s very simple, really.
For any user-partitioned table in MySQL 5.1:
The article that Peter Gulutzan and I wrote a couple of weeks ago is now online:
Here’s a brief outline:
Someone at work pointed out to me this blog post about what to do when disk partitions housing MySQL databases fill up, with the caption, “An idea for [the MySQL 5.1 Partitioning] Docs?”
My reply was, “Nope”.
Well, I was wrong. In a way. This guy’s talking about disk partititions, not partitioned tables. And using symlinks to point to data or database directories is not particularly new or exciting. However, partitioning in MySQL 5.1 provides a kind of cool new way to move table data and index files pretty easily.
DATA DIRECTORY and
INDEX DIRECTORY options have been around for
CREATE TABLE for quite some time, but they’re not supported for
|Showing entries 1 to 6|