MySQL Forums
Forum List  »  Partitioning

Partioning and lots of indices
Posted by: Nic Cottrell
Date: December 06, 2006 04:00PM

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?

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...

Any pointer and tips would be greatly appreciated!

Options: ReplyQuote


Subject
Views
Written By
Posted
Partioning and lots of indices
3371
December 06, 2006 04:00PM


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.