Re: Partition on day of year

[Brian Wilson]

So now, using range partitioning of 2 years like so:

Create Table: CREATE TABLE `test_jobs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`printer_name` varchar(100) NOT NULL,
`job_date_time` datetime NOT NULL,
`username` varchar(100) NOT NULL,
`pages_printed` int(4) NOT NULL,
PRIMARY KEY (`id`,`job_date_time`),
KEY `search` (`customer_id`,`job_date_time`,`printer_name`,`username`)
) ENGINE=MyISAM AUTO_INCREMENT=200000001 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS(`job_date_time`))
(PARTITION p1 VALUES LESS THAN (733831) ENGINE = MyISAM,

And doing this query on 200 million rows:


mysql> select * from test_jobs where customer_id = 1 and job_date_time between '2010-06-14 00:00:00' and '2010-06-14 23:23:59' and printer_name like 'Shar%' and username like 'jo%' order by job_date_time limit 0,25;

I get:

25 rows in set (18.69 sec)


Any ideas on improving that time?