Re: How to improve performance in a table with 70 million rows

[Aftab Khan]

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