1

Here is a sentence:

MySQL can apply partition pruning to SELECT, DELETE, and UPDATE statements. INSERT statements currently cannot be pruned.

So when a new row is inserted MySql can not determine what partition it belongs? Sound very strange. Is it a mistake? or what do they mean by this pharse?

Rick James
  • 135,179
  • 13
  • 127
  • 222
Cherry
  • 31,309
  • 66
  • 224
  • 364
  • Pruning insert statements may not strictly be needed. Although the all the partitions might be locked, they do not have to be read. The one exception I can readily think of is validating records through `unique` constraints, but that should be quite fast across all partitions rather than just one. – Gordon Linoff Nov 25 '15 at 04:50
  • @GordonLinoff is mostly correct... The exception does not exist -- `UNIQUE` and `FOREIGN KEY` are not handled because the code does not want to (and does not attempt to) check all the partitions. – Rick James Nov 27 '15 at 18:24

1 Answers1

2

As I read it, the partition-pruning optimization currently relies on parsing of the WHERE clause to determine which partitions to access. The INSERT statement has no WHERE clause, and the optimizer currently has no other mechanism by which to prune.

It looks as though partition pruning is, at present, something of a work-in-progress.

Darwin von Corax
  • 5,201
  • 3
  • 17
  • 28