MySQL Forums
Forum List  »  Partitioning

Re: Can not partition existings tables
Posted by: Olivier Bourdon
Date: June 07, 2010 03:55AM

Thanks for you infos

see my comments inline

Rick James Wrote:
-------------------------------------------------------
> Around 30 values? And it is a BIGINT?
>

yes because of legacy inherited DB all primary keys have
been coded as bigint

> You will probably have to give up your
> AUTO_INCREMENT.
>
again auto_increment has been inherited
as I need this value to be computed by the DB itself do you
suggest moving this autoincrement to trigger for instance to be able
to use partitioning ?

> A Partitioned table MUST _either_
> * Have no PRIMARY KEY, nor UNIQUE key; _or_
> * Have the partition key in EVERY PRIMARY KEY and
> UNIQUE key.
>
> Without those constraints, it is too costly to
> assure that an INSERT does not violate
> UNIQUEness.
>
> Note: All those extra indexes are pretty useless
> once you Partition the table -- any query that
> wants to use any of them would have to touch every
> partition. That is, Partition "pruning" cannot be
> used to speed things up (in those cases).
>

Do you mean that I should remove all indexes I created for my queries
to run faster ? But then how do working real life examples using partitioning cope with uniqueness and query indexes ??? I am not sure I
follow you there.

> What is your goal in Partitioning? I ask this
> because there are some goals for which
> Partitioning does not help as much as one would
> hope.

In fact some of the items lookalike tables contain quite a big amount
of data (read millions) and all the queries which need to be run in the application
always run on separated values of the seg_id I want to partition on
(you can consider all seg_id 1 data to be completely independant of
seg_id 2 data)
Therefore I thought that partitioning would help drastically the application performance and keep my indexes much smaller

Thanks again for your time & answers

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can not partition existings tables
1739
June 07, 2010 03:55AM


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.