Re: Partitioning performance issues // 5.1
The biggest improvements are when you use the pruning, i.e. can skip big parts of the data, preferable only have one partition left. But if you are doing index lookups it will still not give you a big performance gain (usually indexes is O(log(n)) which means that for every time you double the data, it will only cost you a constant amount of performance...)
The biggest gain partitioning will give you is for select queries where pruning kicks in (i.e the partitioning expression can be evaluated by the WHERE expression and the partitioning expression supports pruning) AND it does not have an index to use, then the partitioning will only scan the matching partitions, instead of the whole table.
And as I wrote before, it will help when managing large amounts of data, since you can alter each partition on its own, instead of rewriting the whole table.
Since you partition on user_id and that is the first part of the primary key, you can only do some minor gains because of indexes in smaller chunks (i.e. not as big as I think you expected, due to how the indexes work, you can do some test your self just by testing with tables of different sizes).
If you partition on the secondary primary key part, and query them without the first part, you would see the approximately query time to be 1/n where n is the number of partitions, but that may or may not be how you use the table.
So no, partitioning is no magic bullet, it has its pro's and con's.
I hope you got some better understanding of partitioning, otherwise please ask a more specific question and I will try to answer that.
Regards Mattias