Re: How should I partition this table?
Posted by:
Rick James
Date: April 29, 2013 08:10AM
> We currently have 36 divisions.
What are the SELECTs? That PARTITIONing may not provide any performance (or other) benefits.
> Use DATETIME instead
Do you know what queries involve the DATE and TIME fields? There are rare cases where keeping the fields separate helps. Most applications benefit from combining them. And often, the code simplifies when combining the fields.
> 40gb smaller than the production
A 5% decrease in size could be simply because of rebuilding the BTree.
New rows are inserted based on DIVISION and DATE+TIME? That says that they are being 'appended' to each of the 36 partitions? (No, I don't draw any useful conclusions from that; just trying to get a feel for things.)
>> SUBPARTITIONs are not useful.
Simply, I have not found a use case that shows any benefit. (I think "performance" is the only possible benefit in PARTITIONing.) If you can describe a use case, I am interested to hear.
> but I suspect that doing so will break lots of their code.
And, with a billion rows, it would take a _long_ time to change the table.
As for the DOUBLEs, if they have not noticed a few cents lost or gained in the past 8 years, maybe it is not a big issue.
With a billion rows, I suspect you have "summary table"?
OK, here is a use case for PARTITION BY Division -- If some query scans all the rows for one division. That would take hours (instead of days) with that partitioning. However, summary tables could possibly get the desired results in minutes, maybe in seconds. That is, I would lobby for a more thorough solution than a partial solution.