MySQL Forums
Forum List  »  Partitioning

Re: missing data after partitioning - bug?
Posted by: Rick James
Date: May 25, 2014 04:27PM

MyISAM or InnoDB?

Do all your INTs need to be that big? Look at MEDIUMINT/SMALLINT/TINYINT, and make them UNSIGNED where appropriate. Saving space will help performance.

SHOW VARIABLES LIKE '%buffer%';

To see if "partition pruning" is being invoked:
EXPLAIN PARTITIONS SELECT ...

> UNIQUE KEY `p3_plays_nodups` (`userid`,`action`,`playper`,`songid`,`timestamp`),

* Was it UNIQUE in the original table?

* It is not good to have a NULLable field (playper) in a UNIQUE KEY?

* timestamps (DATETIMEs) are not good for UNIQUE KEYs; they are too likely to be duplicate.

> i'm afraid i couldn't find any decent how to

Will this do?...
http://mysql.rjweb.org/doc.php/partitionmaint

> performance issues

Let's see the queries that are "too slow". PARTITIONing is _not_ a panacea. _Some_ queries will speed up. Others won't, and may even slow down a little.

> If there's an issue, shouldn't i get a warning or error message?

Immediately after issuing the INSERT, do
SHOW WARNINGS;

select count(*) from p3_dna_new WHERE `timestamp` >= '2016-01-01'; -- Is this how many rows are missing?
See my use of a "future" partition in the link above.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: missing data after partitioning - bug?
1276
May 25, 2014 04:27PM


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.