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]
