MySQL Forums
Forum List  »  Partitioning

Re: Partitioning on data: Without storing data in table.
Posted by: Jonathan Stephens
Date: December 29, 2006 07:26AM

No, it's not possible to partition on columns that aren't stored in the table.

I don't know all the particulars of your situation, but my thoughts are as follows:

1. Disk space is relatively cheap.

2. You can easily partition a large table across multiple disks using mount points for the disks, and the DATA DIRECTORY and INDEX DIRECTORY options for individual partitions.

3. If you perform the partitioning in the application, then: You're going to take a speed hit due to every operation requiring dynamic SQL; You'll have to write and debug the partitioning code yourself rather than letting MySQL take care of this for you; You lose any chance you might have had to benefit from the query cache or from partition pruning.

4. The maximum possible number of all partitions and subpartitions per table in MySQL 5.1 is 1024. However, you don't have to define partitions based on every possible *individual* value. For instance, you could create 100 partitions, each containing a range of 1,000 data point IDs. Also keep in mind that subpartitioning can be done only by [LINEAR] HASH or [LINEAR] KEY.

5. If every combination of data point ID and timestamp (datetime) is guaranteed unique, why do you need to generate a separate primary key? Why not just create a PK on those two columns and use that? IIRC, you can still partition on one column and subpartition on the other.

6. What sorts of queries will you be running on this data? You need to have a clear idea of these before you devise a partitioning scheme if you want to take advantage of pruning.

Those are some things to keep in mind, but it's not possible to say much more without knowing what you plan to do with the data once you've stored it.

Jon Stephens
MySQL Documentation Team @ Oracle
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation

Options: ReplyQuote

Written By
Re: Partitioning on data: Without storing data in table.
December 29, 2006 07:26AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.