MySQL Forums
Forum List  »  Optimizer & Parser

Re: Explain estimate completely wrong for range when partitioned by day
Posted by: Blake Harps
Date: March 04, 2014 12:27PM

Thank you for your response!

We only keep ~48 hours of data for each customer DB in the particular environment, so the original intent was to truncate all partitions > 2 days old each night. We would then have 2 full days of data at all times, but it obviously didn't really work as expected.

However, I've now switched to a partitioning scheme similar to the you wrote about on your site:

Quote

/*!50100 PARTITION BY RANGE (TO_DAYS(created))
(PARTITION p_base VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p_20140301 VALUES LESS THAN (735659) ENGINE = InnoDB,
PARTITION p_20140302 VALUES LESS THAN (735660) ENGINE = InnoDB,
PARTITION p_20140303 VALUES LESS THAN (735661) ENGINE = InnoDB,
PARTITION p_20140304 VALUES LESS THAN (735662) ENGINE = InnoDB,
PARTITION p_20140305 VALUES LESS THAN (735663) ENGINE = InnoDB,
PARTITION p_20140306 VALUES LESS THAN (735664) ENGINE = InnoDB,
PARTITION p_overflow VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

And just have a script loop through and drop the oldest partition and reorganize p_overflow into the newest partition each night.

All the counts are now working as expected and range queries are only hitting the necessary partitions.


@Rick & @Mattias, I appreciate the help in pointing me in the right direction.

Options: ReplyQuote




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.