CODEWITHSUNDEEP
mysqldatabase
egidra
egidra

Reputation: 9087

How to partition MySQL table by day?

I'm running MySQL 5.1 and storing data from web logs into a table. There is a datetime column which I want to partition by day. Every night I add new data from the previous day into the table, which is why I want to partition by day. It is usually a few million rows. I want to partition by day because it usually takes 20 seconds for a MySQL query to complete.

In short, I want to partition by each day because users can click on a calendar to get web log information consisting of a day's worth of data. The data spans millions of row (for a single day).

The problem that I've seen with a lot of partitioning articles is that you have to explicitly specify what values you want to partition for? I don't like this way because it means that I'll have to alter the table every night in order to add an extra partition. Is there a built in MySQL feature to do this for me automatically, or will I have to write a bash script/cron job to alter the table for me every night?

For example, if I were to follow the following example: http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html

In one year, I would have 365 partitions.

Upvotes: 3

Views: 15592

Answers (2)

Rick James
Rick James

Reputation: 142296

Indexes are a must for any table. The details of the index(es) derive from the SELECTs you have; let's see them.

Rules of thumb:

  • Don't partition a table of less than a million rows
  • Don't use more than about 50 partitions.
  • If you are 'purging old data' after some number of days/weeks/months, see my blog for the code on how to do that.
  • PARTITION BY RANGE() is the only useful partition mechanism.

Upvotes: 10

Eric B.
Eric B.

Reputation: 24411

I tried this once. I ended up creating a cron job to do the partitioning on a regular basis (once a month). Keep in mind that you have a maximum of 1024 partitions per table (http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html).

Offhand, I probably wouldn't recommend it. For my needs, I saw this created a significant slowdown in any searches that that required cross-partition results.

Based on your updated explanation, I would first recommend to create the necessary indexes. I would read MySQL Optimization chapter (in specific the section on indexes), to better learn how to ensure you have the necessary indexes. You can also use the slow_query log to help isolate the problematic queries.

Once you have that narrowed down, I can see your need for partitioning change to wanting to partition to limit the size of a particular partition (perhaps for storage space or for quick truncation, etc). At that point, you may decide to partition on a monthly or annual basis.

Partitioning using the date as a partition key will obviously force you into creating an index for the date field. Start with that and see how it goes before you get into the extra efforts of partitioning on a scheduled basis.

Upvotes: 2

Related Questions