1

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance.

  • Would I need to change the applications select statements for the application to continue to work?

  • If it would continue to work with the existing SQL, would I need change the SQL in order to gain any advantage from the partitioning?

  • What can I expect to happen if my application is using a Database Abstraction Layer, where I would normally not know if the DB had been partitioned?

Improve this question

2 Answers 2

Reset to default
0

This may sound naive, however, a partitioned table is essentially no different from any other table from the outside:

select * from non-partitioned-table;

will be no different than a:

select * from partitioned-table;

Hence, for your application, nothing changes.

The big difference is the speed at which a query can be run against a table when using an appropriate where clause.

For example: if you have 1000 lego blocks, equally distributed between 3 different colors, and you are looking for one that you know is green and has your name written on it, you are only going to "partition" your search through the legos by looking only at the GREEN lego blocks. You are only going to look through 1/3 of your whole set.

Similarly, if you partition a table according to a certain column, date is always a good example, and you are trying to find an entry in that table where the approximate time is known, you mysql will only attempt to search only in a subset (a partition if you will) of the whole table. So to answer your question: ensure that if you DO use partitioning, that the majority of your queries will include a where clause referring to the partitioning key.

If my lego example was to abstract, or anything else sounds wierd: i would advice reading up on: http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html

Improve this answer
2
  • Nice clear answer and basically what I was expecting. What made me wonder was seeing some documentation in MySQL that explicitly mentioned a partition in a SELECT statement, but I guess that is for unusual circumstances. Thanks very much.
    – user898617
    Commented Jul 28, 2012 at 5:33
  • To be more explicit on part of what tnosaj was saying: queries not restricting by the partitioned key require scans of all partitions. Also, "explain partitions" instead of just "explain" will show you which partitions it's going to be looking at.
    – atxdba
    Commented Sep 12, 2012 at 3:06
0

Tips and limited uses of PARTITION: http://mysql.rjweb.org/doc.php/ricksrots (Skip down to the PARTITION section.)

Only 1% of the tables (that I see) use or need PARTITION.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.