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?