If you want to use the index, you cannot put the column through any
function. You have to compare the column value, as is, to constants. For
example,
SELECT SUM(OrderSubTotal) FROM tblOrders
WHERE ShipDate BETWEEN '2004-09-01' AND '2004-09-30';
should do what you want.
Michael
Andrew Kreps wrote:
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]
