CODEWITHSUNDEEP
mysqlperformancedatabase-designpartitioningbigdata
Lavi Avigdor
Lavi Avigdor

Reputation: 4182

When not to partition a table (MySQL)?

In MySQL (5.5+), for large tables (500,000,000 rows and up), that you access (read & write) always by specifying the primary key, are there times when partitioning (by the primary key) will not improve performance?

In particular, when each row is very thin (3 int columns).

Reason to ask: For a table with heavy content (multiple varchar/text columns) I've seen a good improvement but in a similar case for thin rows (3 int columns) I think I'm seeing worse performance then without partitions. [both use 100 partitions based on primary key(int)]

Upvotes: 2

Views: 289

Answers (1)

Edmon
Edmon

Reputation: 4872

First, experiment if possible with different types of partitioning:

HASH,RANGE,KEY,LIST and sub-partitioning to see which yields best results in terms of space and time.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

Second, use explain partitions to get statistics on partitioning:

http://dev.mysql.com/doc/refman/5.5/en/explain.html

http://dev.mysql.com/doc/refman/5.5/en/partitioning-info.html

Finally, I suggest looking into this very informative post:

http://chrismoos.com/2010/01/31/mysql-partitioning-tables-with-millions-of-rows/

Upvotes: 3

Related Questions