Re: Partition on day of year

[Rick James]

Is this your only kind of query?
select * from test_jobs2 where job_date_time > '2011-01-01' and job_date_time < '2011-01-01 23:59:59'
(A side note: It excludes midnight from either end of the day.)

If there are other types of queries, then optimizing for this one may de-optimize others.

PARTITION is not needed to optimize that query, a non-PARTITION table with an index on job_date_time will run at about the same speed as anything that PARTITION can deliver.

For non-PARTITION:
PRIMARY KEY (`id`)
INDEX(`job_date_time`),
KEY `search` (`customer_id`,`job_date_time`,`printer_name`,`username`)

Does that query return 600K rows? Shoveling that many rows from the server to the client takes time. No INDEX or PARTITIONing can eliminate the transfer time.