Re: Explain estimate completely wrong for range when partitioned by day

[Blake Harps]

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.