Optimizing with DATE()
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.
Subject
Views
Written By
Posted
Optimizing with DATE()
2986
January 03, 2006 02:28PM
2059
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.