Re: Can not partition existings tables

[Olivier Bourdon]

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