MySQL Forums
Forum List  »  Optimizer & Parser

Re: Problems with query not using expeced indexes
Posted by: Rick James
Date: March 10, 2010 10:28PM

1. Don't quote values that correspond to INT columns; do quote columns that correspond to VARCHAR fields:
AND date_month = '200910'
AND customerid =1
(Or change the datatypes)

2. Use
dateDay >= '2009-10=01' AND dateDay < DATE_ADD(dateDay, INTERVAL 1 MONTH)

3.
INDEX(customerid, processed, dateDay)

4. Why are your grouping by customer id but not displaying it?

All together:
# do once:
ALTER TABLE daily
   ADD INDEX (customerid, processed, dateDay);
# then...
SELECT  SUM( price ) AS total_price,
        DATE_FORMAT(dateDay, '%Y%m') AS date_month
    FROM  daily
    WHERE  processed = 0
      AND  dateDay >= '2009-10=01'
      AND  dateDay < DATE_ADD(dateDay, INTERVAL 1 MONTH)
      AND  customerid = '1'
    GROUP BY  2;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Problems with query not using expeced indexes
1788
March 10, 2010 10:28PM


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.