MySQL Forums
Forum List  »  Partitioning

Re: Simple question about partitioning
Posted by: Phil Hildebrand
Date: June 25, 2008 01:51AM

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)

Options: ReplyQuote

Written By
June 24, 2008 11:34AM
Re: Simple question about partitioning
June 25, 2008 01:51AM

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.