MySQL Forums
Forum List  »  Partitioning

Re: How to handle secondary keys in partitioning?
Posted by: Mattias Jonsson
Date: May 09, 2010 01:23PM

The pruning that is done in prune_partitions() in (i.e. static pruning) is not done for inserts, but since insert is done by write_row(), it is 'dynamically' pruned, since the target partition is calculated before forwarding the write_row call to the correct partition (update_row does the same, but can involve two partitions if it have to move the row between two partitions, i.e. either forward the update_row() call or split it into delete_row() + write_row() to different partitions).

One thing that had to be fixed in partitioning was the 'bulk_insert' calls, which was fixed in bug#35845. Bulk_insert prepares the table (partitions) for inserting lots of rows. MyISAM disables its indexes and repair them after the statement, as an optimization, which is very bad if you did not even insert any rows (as in one row insert into a partitioned table). The fix was to delay the bulk_insert calls for each partition until the partition actually had a row to insert.

Static pruning currently affects row scanning and index scans, some optimizer calls like records_in_range() and some info() operations (search for 'used_partitions' in

UPDATE/DELETE is actually using prune_partitions() (which affects the index and table scans). This since the server always needs to look up the rows to update/delete before updating/deleting them.

DELETE will not need any change (since the WHERE clause is the same as SELECT's).

INSERT should also analyze all inserted rows to see which partitions will be needed, so that only those partitions is opened and locked.

The same for UPDATE, but that will need even more logic (or simply skip pruning if too complex to analyze the resulting rows before open/lock/reading the source rows).

So the answer to your last question is:
No, but it will still open and lock all partitions. The INSERT will directly go to its partitions, and DELETE/UPDATE will search for rows to delete/update just like it does for a select query (i.e. take advantage of pruning if possible).

Options: ReplyQuote

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.