I'm interested in this, too. We have a logging table that sees hundreds of rows per second, and we do a ton of monthly reports. We just bit the bullet and added an indexed DATE column. Is there a better strategy?
____________________________________________________________ Eamon Daly
----- Original Message ----- From: "Andrew Kreps" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 22, 2004 4:48 PM
Subject: Date Indexing
Hey all, I have a question about indexing part of a date field. I have a query that I run on a regular basis to retrieve monthly sales numbers:
SELECT SUM(OrderSubTotal) FROM tblOrders WHERE DATE_FORMAT(ShipDate, '%Y-%m') = '2004-09';
ShipDate is a date field. My question is how I can phrase the query (or re-index ShipDate) so that MySQL uses the ShipDate index? I've tried figuring out how to index part of a date field, and I can't seem to find any way to do that. I've also tried changing the statement to ShipDate like '2004-09%', as well as the MONTH and YEAR functions, but none of them utilize the index. Is there another, more efficient way to phrase it so the index is used?
I'm using MySQL 3.23.53a.
The Explain: | table | type | possible_keys | key | key_len | ref | rows | Extra | | tblOrders | ALL | NULL | NULL | NULL | NULL | 122543 | where used |
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
