I have a table with a
year column that has consecutive values --
2001, and so on. It's a read-heavy MyISAM table, with 25+ million rows for every value of
year and a comparable set inserted in bulk approximately annually.
Most of our queries include a
WHERE year = N condition. In a few cases we do a self-join
ON T1.year = T2.year - 1 or similar. So I'd like to explore partitioning by the year to see if we get any performance benefit. But since I don't store a full date, it's not exactly a range, and since I want on year per partition, it's not exactly a list.
So, assuming that I have a table definition that starts like this:
CREATE TABLE foo ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , year SMALLINT UNSIGNED NOT NULL , ... ) ENGINE=MyISAM
(Note: I'm leaving the bad
PRIMARY KEY in this example so the answer below that addresses it will still make sense - but it's tangential to the real question, and not a problem for my actual schema. For the purposes of answering, please assume
PRIMARY KEY (id, year) is defined instead.)
Assuming every value of
year that might end up in the table is accounted for in one explicit partition (no years < 2001 and no
LESS THAN MAXVALUE partition for the
RANGE case), would the following definitions be equivalent? If not, what's the difference?
-- Definition 1 PARTITION BY RANGE (year) ( PARTITION p0 VALUES LESS THAN (2001) , PARTITION p1 VALUES LESS THAN (2002) , ... ); -- Definition 2 PARTITION BY LIST (year) ( PARTITION p0 VALUES IN (2000) , PARTITION p1 VALUES IN (2001) , ... );