MySQL Forums
Forum List  »  Partitioning

Re: Partitioning not working need primary key?
Posted by: Rick James
Date: October 25, 2012 11:22PM

I don't want to go into the gory details of "binary" versus "decimal" arithmetic. FLOAT and DOUBLE are stored in IEEE754 Binary Floating Point. Here's an experiment (using FLOAT instead of DOUBLE, so that the error would show up sooner):

CREATE TABLE flt102 ( f FLOAT(10,2) NOT NULL );
INSERT INTO flt102 SELECT 1000.01 FROM ints a JOIN ints b;
mysql> SELECT SUM(f), COUNT(*) FROM ( SELECT f FROM flt102 LIMIT 1234 ) x;
+------------+----------+
| SUM(f)     | COUNT(*) |
+------------+----------+
| 1234012.35 |     1234 |
+------------+----------+
I would have expected 1234012.34

Read all about PARTITIONing and how the "partition key" must be part of any UNIQUE index. For one thing, that says that you cannot have
PARTITION BY RANGE(datetime)
and
PRIMARY KEY (`receiptID`,`outletID`)

You could, instead, say
PRIMARY KEY (`receiptID`,`outletID`, datetime)
but then the UNIQUEness check would be on the triple (`receiptID`,`outletID`, datetime).

A table with 3 partitions -- there is perhaps no advantage in such. What do you hope will the advantage be?

Summary table -- I have mentioned it in many answers, especially on the Performance forum. For those from Oracle (etc) worlds, it is a "materialized view", except that the programmer has to do all the work of maintaining the table. I have a very brief discussion here:
http://mysql.rjweb.org/doc.php/ricksrots#data_warehouse

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partitioning not working need primary key?
1599
October 25, 2012 11:22PM


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.