CODEWITHSUNDEEP
mysqldatabasedatabase-partitioning
Travis J
Travis J

Reputation: 82297

How to access / query a database partition in mysql?

When a database table has been partitioned in mysql, how are the individual partitions accessed / queried?

EDIT

In response to @Crack's comment.

So when a partition is in place in a table, then I still would use a normal query. Where does the "pruning" come in, at the database side of the query? Is it pretty much a complex stored Where clause that is applied to every query then? Why are the partitions named if they are not individually accessed?

Upvotes: 3

Views: 1507

Answers (2)

anuragw
anuragw

Reputation: 133

The pruning of data really comes into play mainly when you insert data.

For example, assume I've partitioned a table by hash on id - an integer column, and my hash function is simply checking if the integer is odd/even. So, MySQL would effectively be creating two bins - the odd bin and the even bin.

When I insert id = 1, MySQL applies the hash function. Since the result is odd, the data is put in the odd bin. When I insert id = 2, the data would go to the even bin.

Querying doesn't involve any pruning, just a bit of smart logic. MySQL knows from a query fired on this table that it could potentially improve performance if it could only look at one partition (half the data in our case). So, it attempts to identify the partition.

When a query is now fired involving the id column in a where, MySQL would again apply the hash function to the value passed. Suppose i say WHERE id = 2 AND <some other condition>, the hash returns even. So, now MySQL only looks at the even bin.

In this trivial example, you can see how when querying/inserting data, only one half of the complete data set needs to be scanned/updated, effectively improving my performance by approx. 2 times (let's discount the hashing overhead for now).

Upvotes: 3

piotrp
piotrp

Reputation: 3884

Ok, let's take this one part at a time.

So when a partition is in place in a table, then I still would use a normal query.

Yes. Partitioning is transparent to you, it is meant to optimize (when well used) query performance by dividing physical. storage of data and indexes into separate "bins".

Where does the "pruning" come in, at the database side of the query? Is it pretty much a complex stored Where clause that is applied to every query then?

Yes and no. Depending on partitioning schema, MySQL puts your data into disjoint "bins". Later it reads the WHERE clause of your query and knows which partitions it must check to answer it. MySQL documentation has a few nice examples in documentation: Partition Pruning.

It allows you to store each partition on different physical storage device and MySQL can run some operations in parallel or don't scan some partitions at all (see examples from link above).

Why are the partitions named if they are not individually accessed?

They are individually accessed, but you don't make this decision - optimizer does it. Partition names make it easier for you to manage them. You can find possible operations in documentation (Partition Management).

Since MySQL 5.6.2 you can select data from individual partitions, see Partition Selection. Just an advice - don't use this syntax if you don't have to, because using it makes your queries bound to storage structure of your data (and don't use an unstable version of MySQL in production ;).

Upvotes: 4

Related Questions