General context
I want to be able to tell, when inserting into non-balanced RANGE-partitioned MySQL tables with AUTO INCREMENT primary keys, whether my inserts are causing MySQL to communicate in any way with partitions other than the ones I specify. This is useful for budgeting future capacity for large-scale data loading; with that assurance, I could much more accurately predict that performance and hardware resource cost of loading data into the database.
I am using MySQL 5.6.
Specific context
Say I have the following table in MySQL (5.6):
CREATE TABLE foo (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(6) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!12345 PARTITION BY RANGE (id)
(PARTITION cold VALUES LESS THAN (8000) ENGINE = InnoDB,
PARTITION hot VALUES LESS THAN (9000) ENGINE = InnoDB,
PARTITION overflow VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
Assume the table is not sparse: no rows have been deleted, so count(*) = max(id) = 9001.
Questions
If I do INSERT INTO foo (data) PARTITION (hot) VALUES ('abc') or an equivalent LOAD DATA statement with the PARTITION clause included, are any partitions other than the selected hot partition being accessed?
How would I tell what partitions are being accessed by those DML statements?
What I've tried
The MySQL documentation on partition selection says:
REPLACE and INSERT now lock only those partitions having rows to be inserted or replaced. However, if an AUTO_INCREMENT value is generated for any partitioning column then all partitions are locked.
Additionally, it says:
Locks imposed by LOAD DATA statements on partitioned tables cannot be pruned.
Those statements don't help clarify which partitions are being accessed by DML queries which explicitly specify the partition.
I've tried doing EXPLAIN PARTITIONS INSERT INTO foo ..., but the partitions column of the output is always NULL.
According to the documentation,
For statements that insert rows, the behavior differs in that failure to find a suitable partition causes the statement to fail. This is true for both INSERT and REPLACE statements
So when you try to insert a row that does not match your specified partition, you'll receive
Error Code: 1748. Found a row not matching the given partition set
This including statements where some rows match and some don't, so you cannot use this to fill "hot" and throw away rows that would go into "overflow" (as the whole query will fail).
The explain-otuput for MySQL 5.6 does not include a seperate row for insert; the value for partition relates to the source of the data you insert (in cases where you e.g. use insert ... select ... partition ...), even if you use values() (then you use "no table", and the relevant partition is just null). For MySQL 5.7+, there is an "insert"-type, and it would indeed list only your specified partition.