Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will hold 180-260 million records. As it will be properly indexed table, so I am thinking of limiting the table records to 20 million this way I would have to create 9-13 tables.

But I am not quite sure about how it will improve the performance because they will be sitting on same machine (32GB RAM)?

I am using MySQL and tables would be MyISAM and big table would have index on id field and there are not further complexities like full text search etc.

Please also shed light on table partitioning vs database partitioning.

share|improve this question
Please explain what type of indexed searching will be performed against the table other than the id. It will clue you in on the type of partitioning to be done. – RolandoMySQLDBA Oct 7 '11 at 19:33
It will be only id. – Rick James Oct 7 '11 at 19:38
'Only id' still doesn't tell us anything. How are the ids distributed amongst the range of all ids? Are you mainly querying for the newer ones, is it truely distributed? Will the data access be mostly read or mostly write? All of these are important questions we need answers to before we can help you specifically. That said, the answers below are really useful ones :) – Walter Heck Oct 11 '11 at 19:35
[Here] are my feelings 4 years after starting this thread. – Rick James Jun 28 '15 at 18:23
up vote 23 down vote accepted

The following is just insane ranting and raving...

If you leave all data in one table (no partitioning), you will have O(log n) search times using a key. Let's take the worst index in the world, the binary tree. Each tree node has exactly one key. A perfectly balanced binary tree with 268,435,455 (2^28 - 1) tree nodes would be a height of 28. If you split up this binary tree into 16 separate trees, you get 16 binary trees each with 16,777,215 (2^24 - 1) tree nodes for a height of 24. The search path is reduced by 4 nodes, a 14.2857 % height reduction. If the search time is in microseconds, a 14.2857 % reduction in search time is nil-to-negligible.

Now in the real world, a BTREE index would have treenodes with multiple keys. Each BTREE search would perform binary searching within the page with a possible decent into another page. For example, if each BTREE page contained 1024 keys, a tree height of 3 or 4 would be the norm, a short tree height indeed.

Notice that a partitiioning of a table does not reduce the height of the BTREE which is already small. Given a partitioning of 260 milliion rows, there is even the strong likelihood of having multiple BTREEs with the same height. Searching for a key may pass through all root BTREE pages every time. Only one will fulfill the path of the needed search range.

Now expand on this. All the partitions exist on the same machine. If you do not have separate disks for each partition, you will have disk I/O and spindle rotations as an automatic bottleneck outside of partition search performance.

In this case, paritioning by database does not buy you anything either if id is the only search key being utitlized.

Partitioning of data should serve to group data that are logically and cohesively in the same class. Performance of searching each partition need not be the main consideration as long as the data is correctly grouped. Once you have achieved the logical partitioning, then concentrate on search time. If you are just separating data by id only, it is possible that many rows of data may never be accessed for reads or writes. Now, that should be a major consideration: Locate all ids most frequently accessed and partition by that. All less frequently accessed ids should reside in one big archive table that is still accessible by index lookup for that 'once in a blue moon' query.

The overall impact should be to have at least two partitions: One for frequently accessed ids, and the other paritiion for the rest of the ids. If the frequently accessed ids is fairly large, you could optionally partition that.

share|improve this answer

200 million rows is certainly in the range where you could benefit from table partitioning. Depending on your application, you could bet some of the benefits listed below:

  • Ease of purging old data If you need to clear down records more than (say) 6 months old, you can partition the table on the date and then swap out older partitions. This is much faster than deleting data from a table and can often be done on a live system. In the OP's case this might be helpful for system maintenance.

  • Multiple disk volumes Partitioning allows you to split data to distribute disk traffic across multiple disk volumes for speed. With a modern RAID controller this isn't likely to be an issue for the OP.

  • Faster table and range scans Really, an operational system shouldn't be doing this sort of thing, but a data warehouse or similar system will do this sort of query in quantity. Table scans use mainly sequential disk traffic, so they are typically the most efficient way to process a query that returns more than a few percent of the rows in a table.

    Partitioning by a common filter (typically time or period based) allows large chunks of the table to be eliminated from such queries if the predicate can be resolved against the partitioning key. It also allows the table to be split over multiple volumes, which can give significant performance gains for large data sets. Normally, this is not an issue for operational systems.

For the OP's purposes partitioning isn't likely to achieve much performance benefit for operational queries, but it may be useful for system management. If there is any significant requirement to report aggregates across large volumes of data then an appropriate partitioning scheme may help with that.

share|improve this answer

Partitioning allows concurrent reorgs by partition, if all of your indexes are partitioned. If not, the partitions are still much smaller and use less workspace to reorg. And, internally, any "good" DBMS can do things in parallel with partitioned tables. That likely does NOT include MySQL or MyISAM, tho....

share|improve this answer
MySQL does no parallel processing, even when partitioning is involved. MySQL indexes only one partition; hence UNIQUE and FOREIGN KEY are not really available in partitioned tables. Partitioning on MyISAM versus InnoDB -- no difference with respect to the things discussed in this thread. – Rick James Jun 28 '15 at 18:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.