Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer
    
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 Jul 28 '12 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 Sep 12 '12 at 3:06

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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