CODEWITHSUNDEEP
mysqlquery-optimizationdatabase-partitioning
Pooya Estakhri
Pooya Estakhri

Reputation: 1299

Mysql select by auto increment primary key while partitioned by date

I was wondering how would mysql act if i partition a table by date and then have some select or update queries by primary key ?

is it going to search all partitions or query optimizer knows in which partition the row is saved ?

What about other unique and not-unique indexed columns ?

Upvotes: 0

Views: 396

Answers (1)

Rick James
Rick James

Reputation: 142518

Background

Think of a PARTITIONed table as a collection of virtually independent tables, each with its own data BTree and index BTree(s).

All UNIQUE keys, including the PRIMARY KEY must include the "partition key".

If the partition key is available in the query, the query will first try to do "partition pruning" to limit the number of partitions to actually look at. Without that info, it must look at all partitions.

After the "pruning", the processing goes to each of the possible partitions, and performs the query.

Select, Update

A SELECT logically does a UNION ALL of whatever was found in the non-pruned partitions.

An UPDATE applies its action to each non-pruned partitions. No harm is done (except performance) by the updates that did nothing.

Opinion

In my experience, PARTITIONing often slows thing down due to things such as the above. There are a small number of use cases for partitioning: http://mysql.rjweb.org/doc.php/partitionmaint

Your specific questions

partition a table by date and then have some select or update queries by primary key ?

All partitions will be touched. The SELECT combines the one result with N-1 empty results. The UPDATE will do one update, plus N-1 useless attempts to update.

An AUTO_INCREMENT column must be the first column in some index (not necessarily the PK, not necessarily alone). So, using the id is quite efficient in each partition. But that means that it is N times as much effort as in a non-partitioned table. (This is a performance drag for partitioning.)

Upvotes: 1

Related Questions