Re: Partioning a table with a big index, does it help?

[Phil Hildebrand]

I assume you are using MyISAM if you don't have a primary key?

Depending on the percentages of what you mean by 'most' (IE: 50% or 90%), partitioning should help, as long as your queries would not be using ranges that would be contained in all of the partitions.

I believe you'd have to make a primary key in order to partition the data (even if it's all 3 keys), because of how partitioning currently works - the partitioned key needs to be part of the primary key.

Without knowing what the queries are (but making some assumptions by the names and that questid is in most of your application queries), I would suggest trying one of the following:

If your questid's are just an auto increment, and so every new questid is incremented, then:

make the primary key start with questid - (questid,answer) or something like that.

Then use a partition by key type:

alter table x partition by key partitions 50

or a hash type:

alter table x partition by hash (mod(questid,50)) partitions 50

If the questid's have some meaning (like questid's < 100 belong to x, <500 belong to y), then it would might make sense to use a range partition.

Note: each new table will be a new file

Verify use of your partitions with your most common queries. The indexes will be associated with each partition (IE - there's no global index per say, so the answer index will only contain entries for the given partition).