Re: Partioning and lots of indices

[Mikael Ronström]

Hi,


Nic Cottrell Wrote:
-------------------------------------------------------
> Hi All!
>
> I have an InnoDB table with 30M rows and the
> following structure:
> +----------------+----------------------+------+--
> ---+---------+-------+
> | Field | Type | Null |
> Key | Default | Extra |
> +----------------+----------------------+------+--
> ---+---------+-------+
> | FREQUENCY | smallint(5) unsigned | NO |
> MUL | 0 | |
> | JDOID | int(10) unsigned | NO |
> PRI | | |
> | WORD1_JDOID | int(10) unsigned | NO |
> MUL | 0 | |
> | WORD2_JDOID | int(10) unsigned | NO |
> MUL | 0 | |
> | LANGUAGE_JDOID | int(10) unsigned | YES |
> MUL | NULL | |
> +----------------+----------------------+------+--
> ---+---------+-------+
> and the following indices:
> +-------------+------------+-----------------+----
> ----------+----------------+-----------+----------
> ---+----------+--------+------+------------+------
> ---+
> | Table | Non_unique | Key_name |
> Seq_in_index | Column_name | Collation |
> Cardinality | Sub_part | Packed | Null |
> Index_type | Comment |
> +-------------+------------+-----------------+----
> ----------+----------------+-----------+----------
> ---+----------+--------+------+------------+------
> ---+
> | coincidence | 0 | PRIMARY |
> 1 | JDOID | A |
> 235509199 | NULL | NULL | | BTREE
> | |
> | coincidence | 0 | WORD1_JDOID |
> 1 | WORD1_JDOID | A |
> 10704963 | NULL | NULL | | BTREE |
> |
> | coincidence | 0 | WORD1_JDOID |
> 2 | WORD2_JDOID | A |
> 235509199 | NULL | NULL | | BTREE
> | |
> | coincidence | 1 | I_CONCDNC_WORD2 |
> 1 | WORD2_JDOID | A |
> 8121006 | NULL | NULL | | BTREE |
> |
> | coincidence | 1 | FREQUENCY |
> 1 | FREQUENCY | A |
> 15 | NULL | NULL | | BTREE |
> |
> | coincidence | 1 | LANGUAGE_JDOID |
> 1 | LANGUAGE_JDOID | A |
> 15 | NULL | NULL | YES | BTREE |
> |
> +-------------+------------+-----------------+----
> ----------+----------------+-----------+----------
> ---+----------+--------+------+------------+------
> ---+
>
> I've been reading up on partioning and wonder how
> I can go about it effectively here. I can
> partition by JDOID since lookups are almost always
> by WORD1_JDOID or WORD2_JDOID so all partitions
> would have to be searched every query anyway. I
> would like to partition by language_jdoid, but the
> partition key needs to be part of the primary key,
> right?
>

partition key needs to be part of primary key yes. In some
cases it is possible to extend the primary key so maybe here
it is possible to have PK=(JDOID, language_JDOID).

> Will I get performance improvement by partioning
> even if it doesn't help the query optimizer ignore
> certain partions? This table is over 40GB now, so
> at least it may be possible to load the entire
> index of a partition into memory at one time...
>

This is probably not the case given that each index scan
is turned into one per partition.

Rgrds Mikael

> Any pointer and tips would be greatly appreciated!