Re: Performance doubt

[Mattias Jonsson]

Opening of partitions is not changed in 5.6, but since open tables are reused by the table_cache, it is only a cost taken when opening a new instance of the table, not for each statement.

What is improved is the reduced number of lock calls, by delaying the call for locking the storage engine until after partitioning pruning is done. So that only the used partitions will call for locking.

I.e. in 5.6 a Primary Key lookup only uses one partition and will only call the underlying storage engine to lock once, but in 5.1/5.5 even a PK lookup would do the lock call for every partition, resulting in bad performance for heavily partitioned tables, even if only one partition was read.