MySQL Forums
Forum List  »  Partitioning

Re: Partioning and lots of indices
Posted by: Mikael Ronström
Date: December 07, 2006 12:49AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
3363
December 06, 2006 04:00PM
Re: Partioning and lots of indices
2232
December 07, 2006 12:49AM


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.