MySQL Forums
Forum List  »  Performance

Re: How to improve performance in a table with 70 million rows
Posted by: Aftab Khan
Date: August 08, 2012 04:53AM

Inserting single rows in partitions is slow. Whenever you insert a record, the partitioning engine locks the entire table. When you insert thousands of records, the partitioning engine will lock all partitions before the insert, and unlock them after the insert.

Partitioning performance drops drastically with hundreds of partitions,
http://bugs.mysql.com/bug.php?id=37252

You use INSERT with multiple records (bulk insert), so you should be okay but you don't purge historic data (probably you cannot). To start with I suggest :

ALTER TABLE user_data
PARTITION by hash (userID)
PARTITIONS 10;


This means when you’re doing lookup by partitioned key (userID) you will look at one (or some of) partitions, however lookups by other keys will need to perform lookup in all partitions and hence can be a lot slower. The gain from updates typically comes from having smaller BTREE on the active partition(s) which allows for a lot better fit in memory.


>`id` int(10) unsigned NOT NULL AUTO_INCREMENT
I don't think you require AUTO_INCREMENT as you already have userID Primary key

Options: ReplyQuote




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.