mysql partitioning

Question

just want to verify that database partition is implemented only at the database level, when we query a partitioned table, we still do our normal query, nothing special with our queries, the optimization is performed automatically when parsing the query, is that correct?

e.g. we have a table called 'address' with a column called 'country_code' and 'city'. so if i want to get all the addresses in New York, US, normally i wound do something like this:

select * from address where country_code = 'US' and city = 'New York'

if now the table is partitioned by 'country_code', and i know that now the query will only be executed on the partition which contains country_code = US. My question is do I need to explicitly specify the partition to query in my sql statement? or i still use the previous statement and the db server will optimize it automatically?

Thanks in advance!

Answer 1

The partitioning is transparent to any queries you write. There is no need to specify what partition to query.

The database just uses the partitions underneath to optimize accordingly and ideally reducing the amount of data or indexes it needs to search through based on you where clause.

Answer 2

Jarod's answer is correct and fully answers the question but I think there's an important piece missing.

If your query doesn't include a filter condition on the partition column, you're likely to see worse performance than you would on an unpartitioned table of the same size.

If you changed your query to just filter on City, it won't use only the US partition and will perform worse than a query against the same yet unpartitioned table.

The only other place where partitioning can help queries is when joining to a partitioned table with the same partition key. Often that join can be parallelize easily. Assuming that's not what you're doing.