MySQL Forums
Forum List  »  Partitioning

Re: Partitioning performance issues // 5.1
Posted by: Mattias Jonsson
Date: February 19, 2010 02:56PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4046
February 17, 2010 10:53AM
2234
February 19, 2010 10:11AM
Re: Partitioning performance issues // 5.1
2867
February 19, 2010 02:56PM


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.