Partitioning, new with MySQL 5.1, has complicated interactions with queries and indexes.  If one isn’t careful it is easy to degrade performance.   For example, select queries that go with that grain (queries where partition elimination occurs) can be much quicker, but select queries that go against that grain can be much slower.   Queries that go against the grain must query each partition, so for a table with 12 partitions,  one query against that  table can result in 12 queries, one against each of the partitions.  An example of this would be a query against a month partitioned table that is looking to see how much activity a product had in the past 12 months. 

The ideal partitioning scheme would be a system where all queries only needs to access data from one

It appears that my presentation isn’t available at the conference site. I’ve added it below.   Sorry for the delay. 

Without some context that I talked about at the conference the presentation may not make sense.

Partitioning creates a grain in the table.  Select queries that go with that grain can be quicker, at times much faster, but select queries that go against that grain can be slower, at times much slower. 

An example is a table that is partitioned by date and has an orderId primary key.  Queries that select data by date will either be almost as fast (partitioning can add a slight overhead) as the non-partitioned table to much faster.   But queries that don’t query by date will have to query all the partitions.  A table partitioned 12 times, one partition for

This article accompanies the slides from a presentation on database sharding. Sharding is a technique used for horizontal scaling of databases we are using at Netlog. If you're interested in high performance, scalability, MySQL, php, caching, partitioning, Sphinx, federation or Netlog, read on ...

This presentation was given at the second day of FOSDEM 2009 in Brussels. FOSDEM is an annual conference on open source software with about 5000 hackers. I was invited by Kris Buytaert and Lenz Grimmer to give a talk in the MySQL Dev Room. The

Here are the slides from yesterday's presentation about horizontal database scaling through sharding at the mySQL dev room at FOSDEM 2009.

I've got a ton of notes and remarks to these slides, which will become available here soon.

I have been using partitioning in MySQL 5.1 since it has been in late beta, and with the odd bump I’ve been quite satisfied with its stability and ease of use. Perhaps the days of maintaining nasty application logic to maintain tables like table_yyyymm manually are finally over!

While some control freaks out there might still prefer the painful approach of manually keeping dated tables and using merge tables/views to link them together, you still have a lot of flexibility in how you manage individual partitions with the ALTER TABLE REORGANIZE/CHANGE/ADD PARTITION commands.

One trick i’ve discovered to work pretty well, is that you can swap in and out partitions right out from under MySQL’s nose. This can be useful in some situations.

WARNING!! This is not supported by MySQL to the best of my knowledge, and possibly is a risky, bad idea. Proceed only if you think you

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides can be downloaded as a

With all due respect to Monty (and I mean that — much respect is due), I have some serious issues with his portrayal of the 5.1 release.  I hate to make my first entry on Planet MySQL about a controversy, but he encouraged people to blog about their experience with 5.1, so that’s what I’ll do here.

Overall Quality

As a long time user, I am very confident that the quality of 5.1 GA far exceeds that of the initial 5.0 GA release (5.0.15).  In fact, I would go further and suggest that the MySQL organization has if anything been too conservative about declaring 5.1 GA.

It’s obviously true that there are still many bugs open.  However no software is bug free, especially not those with codebase as large as MySQL.  So the question is not if they are bug free,

CREATE TABLE t1 ( d DATE ) 
PARTITION by range (to_days(d))
(
  partition p001 VALUES LESS THAN (to_days('2001-01-01')) 
, partition p002 VALUES LESS THAN (to_days('2001-02-01')) 
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);