MySQL Forums
Forum List  »  Partitioning

Re: Performance of multiple separate tables better than that of multiple partitions?
Posted by: Mikael Ronström
Date: March 13, 2007 09:59PM

Hi,
Splitting very large tables has very positive impact on the
insert speed but this obviously benefits both solutions.
Partitioning has some special partition pruning support
which is probably possible for the SQL optimiser to also
discover using multiple tables in some cases, but I'm sure
there are lots of cases where it is better to use partitioning.

In the SELECT queries you provide I'm not sure how a non-partitioned
table is handled, will it be a join or will the application handle
the partitioning. So from what I understand you can probably achieve
all the benefits of partitioning at user-level but obviously it requires
less work on application level if you use the partitioning solution.

Rgrds Mikael


Thorsten Mueller Wrote:
-------------------------------------------------------
> Hi,
>
> I have a general question regarding performance:
> Are partitioned Tables equal to the same number of
> independent tables regarding performance?
>
> Maybe I can explain my problem by an example:
>
> I have a created a table "Hits" with more than
> 50.000.000 lines refering to 5 different
> "categories". (Referencing uses a secondary
> key=int in both cases).
>
> There are two possibilities:
>
> Partitioning the large "Hits" table in say 5
> partitions by using KEY partitioning on a
> secondary key in the Hits table.
>
> The other possibility would be to create 5
> different "Hits" tables for each kategory
> ("HitsA", "HitsB", ...).
>
> The usecases I am thinking of are thousands of
> INSERTS into the "Hits" tables or a multitute of
> Select queries like "SELECT h.* from Hits h,
> Kategory k where k.id = xy AND h.kategory_id =
> k.id "
>
>
> It would be great if anybody could help me with
> this problem.
>
> Thanks,
>
> Thorsten

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance of multiple separate tables better than that of multiple partitions?
2509
March 13, 2007 09:59PM


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.