Re: Simple question about partitioning

[Phil Hildebrand]

Most likely, you're primary key lookup won't be noticeably slower with a partitioned table than without. I believe the only real difference is that MySQL will have to open more files (partitions) to seek to the given key/row.

It may be different as well between different storage engines. Innodb will use a clustered index on a primary key (I'm not sure about MyIsam), so one's you've found your key in the index, you've found the associated row that goes with it - no additional lookup for the row.

I'm assuming your primary key when partitioned is something like (unique_id, group_id) ?

As far as FK's supported with partitioning... as far as I've heard, it won't come until after 6.0, so I doubt it will be soon. When it does come, however, a FK lookup into a partitioned table that's partitioned on all or part of the PK in the foreign table should be able to prune out non-used partitions.

So, if you did a join to t from a (table with fk) where t.unique_id = a.unique_id and t.group_id = a.group_id, then it should only have to look in partitions of t that contain a.group_id. (That would be my expectation anyway)