I have a table which contains 3.5M rows of sales data (~4 years).

Currently some of my queries (done from php) take 6-15 seconds to complete. (indexes are useful, as per explain plans). My next step is to partition the table to see if I can squeeze more out of it, without upgrading hardware during development phase of the application.

When the table is queried, at the very most a whole, single year's worth of data is retrieved, sometimes finer like QTR or month.

When the application is 'released', it is anticipated that the data in the table will grow, at a rate of about 900k rows per year.

This being the case. Should I partition only the amount of years I hold in the db currently? assuming that number can be changed down the line. Or should I create more partitions?

The thought which led me to my question is: If I create more partitions than I have years, then queries will need to get data from multiple partitions, will the performance of this case be worse than if my query is only needing to select data from 1 much larger partition?

  • On a second thought, I do agree. How can this be migrated? – Adam Copley Feb 16 '16 at 18:59
  • When you get 5 migration votes, it gets migrated. – RC. Feb 16 '16 at 20:18

Do not PARTITION unless you have actions that would benefit from it. Let's see your queries. And, will you be purging 'old' data?

Rule of Thumb: Don't even consider PARTITION unless there is more than a million rows.

Rule of Thumb: Have 20-50 partitions; no more.

Do not create next month's partition until you are about to need it. Keep an empty 'future' partition in case you forget to create the next one.

For real performance, use Summary tables.

More on partitioning.
More on Summary tables.

SHOW CREATE TABLE -- There may be some simple changes to make things more efficient. Also provide a sample SELECT.

  • I have posted a new question with the extra information you mentioned in your answer, as I believe it's beyond the scope of the question title. (dba.stackexchange.com/questions/129513/…) – Adam Copley Feb 17 '16 at 10:20
  • Note in my reply there -- I did not mention PARTITION as being a useful solution; it would not help. – Rick James Feb 17 '16 at 19:10
  • Thanks Rick, the advice is much appreciated, I will implement all that you have mentioned, and inform you of the outcomes – Adam Copley Feb 17 '16 at 19:11
  • I am looking forward to hearing the results. With this many 'changes' being recommended, it may take another iteration to get it working efficiently. – Rick James Feb 17 '16 at 19:26

Should I partition only the amount of years I hold in the db currently?

No. Partition conditions fulfill all possible cases. In your case, let's imagine that you have data from 2014 to 2016. You should have a condition for "< 2015-01-01", "< 2016-01-01", "< 2017-01-01", "< Max value", and... "< 2018-01-01" (this last one should be before the lower than Max). The reason is so that you don't have to create the next partition ASAP as that condition fulfills. This doesn't give you a performance hit, as this partition will be empty and, normally wouldn't be considered as a possible partition to retrieve data.

Also, it's perfectly normal to have 1, 2 or positions in advance, as you would need to have less maintenance time, creating new partitions, and to not take the risk of needing to move data from the last partition (< Max) to the new partition.

I would consider too to create smaller partitions, like semesters or trimesters. But an year should be enough.

Remember that the partition column should be part of the primary key and, more important, all your queries should use the partition column in their criteria, or mySQL would need to search for your data in all the partitions, which may have lower performance that if you wouldn't have partitions.

But I wonder if you need to go for partitions right now. 3.5 million rows aren't that much, and you might consider to make some tuning to your mysql out to your queries before refactoring your tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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