MySQL Forums
Forum List  »  Partitioning

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partitioning not working need primary key?
1962
October 23, 2012 09:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.