Re: Partitioning not working need primary key?
Posted by:
Rick James
Date: October 23, 2012 09:50AM
Sorry, but I must digress into other things that will cause you more trouble than PARTITIONing...
> `receiptDate` date NOT NULL,
> `receiptTime` time NOT NULL,
In most situations, it is better to have a single DATETIME field.
INT(2) is not what you think.
> (`receiptID`,`outletID`)
Is that the "natural" PRIMARY KEY?
Do you need a UNIQUEness constraint on that pair? (You will lose it in PARTITIONing.)
How many rows will be in the table eventually? (Need more than a million to make PARTITIONing worth doing.)
Will you be DROPping an old PARTITION each day? (Does not smell like it. This feels like data that you want to keep forever.)
How many days will the table eventually contain? (More than, say, 50, will have troubles of its own.)
> Amount` double(10,2)
NO. That gives you two roundings when you insert data. It could lead to errors eventually.
Use DECIMAL(10,2) if
* You are working in a currency with exactly 2 decimal places, and
* You will not get values bigger than 99,999,999.99.
What SELECTs will hit this table? So far, you are ready for
SELECT ... WHERE receiptid=... AND outletid=...
but not much else. In particular, you are not ready for
SELECT SUM(...) ... WHERE outletid=...
or
SELECT SUM(...) ... GROUP BY outletid
You would need to reverse the order of the PK fields to benefit those queries.