Ok, I have 2 million rows. Each has a billing date. i'm trying to partition this out by that date. I've made partitions. I've checked /var/lib/mysql and the files are there, all of an appropriate file size so I can see something's working. However when I do explian paritions select, its still telling me it's using all the partitions. Am I doing someething wrong with how I built the table? Query the table? Indexes?
CREATE TABLE `billing_bil` (
`id_bil` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
`bill_date_bil` date NOT NULL
PRIMARY KEY (`id`,`bill_date_bil`),
KEY `bill_date_bil` (`bill_date_bil`),
KEY `type_bill_date_bil` (`type_bil`,`bill_date_bil`)
) ENGINE=MyISAM AUTO_INCREMENT=2310168 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(bill_date_bil))
(PARTITION p0 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2011) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2013) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2014) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
EXPLAIN PARTITIONS SELECT SQL_NO_CACHE id_bil FROM billing_bil WHERE bill_date_bil < '2010'
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE billing_bil p0,p1,p2,p3,p4,p5,p6,p7 index bill_date_bil PRIMARY 6 (NULL) 2310167 Using where; Using index
I was expecting that query to use p0 and p1 only, but it uses all of them.
Your partitions are fine. Your query's WHERE clause is where the problem lies. When I ran the EXPLAIN PARTITIONS statement on MySQL 5.5, there were two warnings:
+---------+------+------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2010' for column 'bill_date_bil' at row 1 |
| Warning | 1292 | Incorrect date value: '2010' for column 'bill_date_bil' at row 1 |
+---------+------+------------------------------------------------------------------+
If you change your query to use a real date like so, it will use the partitions (I didn't include your indexes in my CREATE TABLE statement, which is why they aren't being used):
mysql> EXPLAIN PARTITIONS SELECT SQL_NO_CACHE id_bil FROM billing_bil WHERE bill_date_bil < '2010-01-01';
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | billing_bil | p0,p1,p2 | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+-------+
I'm guessing it uses partition p2 because MySQL supports partial dates (2010-00-00 is less than 2010-01-01): How to deal with "partial" dates (2010-00-00) from MySQL in Django?