Have Geek Brain

Splitting mysql

just want to check that the database partition is only implemented at the database level, when we query the partitioned table, we still execute our normal query, nothing special with our queries, the optimization is done automatically when the query is parsed, right?

eg. we have a table called "address" with column "country_code" and "city". so if I want to get all addresses in New York, USA, usually I did something like this:

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

if now the table is split by 'country_code' and I know that now the query will only be executed in the section that contains country_code = US. My question is, do I need to explicitly specify the section for the query in my sql statement? or am I still using the previous statement and the db server will automatically optimize it?

Thanks in advance!

+2


a source to share


2 answers


The separation is transparent to any queries you write. There is no need to specify which section to request.



The database only uses partitions to optimize accordingly and ideally reduce the amount of data or indexes needed for searches based on your where clause.

+1


a source


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

If your query does not include a filter condition in a partition column, you will likely see worse performance than an unallocated table of the same size.



If you change your query to just filter for a city, it will not only use the US partition and will perform worse than a query on the same non-partitioned table.

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

+4


a source






More articles:


All Articles