Re: Partition on day of year

[Rick James]

Oops, it was Shantanu Oak's post that mentioned InnoDB. Most things apply to either Engine. One change: Add id thus:
KEY `search` (`customer_id`,`job_date_time`,`printer_name`,`username`, id)
That will make the subquery approach efficient for MyISAM.

Even if customer_id is always 1, it should be included in the compound indexes where mentioned. Searching on just customer_id would ignore the indexes and do a table scan.

SELECT * FROM test_jobs WHERE customer_id = 1 AND job_date_time BETWEEN '2011-01-02 00:00:00' AND '2011-01-02 23:59:59' ORDER BY job_date_time LIMIT 0,25;
can stop after only 25 rows. Adding to the WHERE clause says it has to scan all the rows with (customer_id = 1 AND job_date_time...) and check the other things before finding the desired 25.

Try adding id to the index (above), then see how fast this runs:
SELECT id (not '*') ...
I expect it to be somewhere between 1 and 30 seconds.

That's the subquery. Then, when it is put in the outer query, it will need to SELECT * to get the 25 (only 25) rows. This will take another 1 second. Total time might be 10-20 seconds. Not great, but better than 30. And PARTITIONing is irrelevant (in this case).