MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing with DATE()
Posted by: Paul Hartman
Date: January 03, 2006 02:28PM

Hi,

I keep of log of when receipts for shipments were printed in batches. I want to print a summary of how many receipts were printed in each batch for today's date. `printed_receipt_time` is of datetime type, and it is indexed. I first tried to do:

select printed_receipt_time,count(*) from shipments
where date(printed_receipt_time)=date(now())
group by printed_receipt_time
order by printed_receipt_time

Results were correct, but it had to scan the entire table because of my use of the DATE() function, which is understandable. My choice is, apparently, either to add another column to the database containing only the DATE without a time, or do to this:

select printed_receipt_time,count(*) from shipments
where printed_receipt_time>=date(now()) and printed_receipt_time<date(adddate(now(),1))
group by printed_receipt_time
order by printed_receipt_time

This seems to work good, it reads only the relevant rows (around 2000, instead of 150000+) and is much faster.

Is there a "better" solution to accomplish this? I'm using MySQL 5.0.

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing with DATE()
2668
January 03, 2006 02:28PM
1896
January 03, 2006 06:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.