Re: Problems with query not using expeced indexes

[Rick James]

SELECT  customerid,
        customerid AS customer_id,   -- Eh? This seems redundant
        SUM( price ) AS amount,
        DATE_FORMAT( dateDay, '%Y' ) AS year,
        DATE_FORMAT( dateDay, '%m' ) AS MONTH
    FROM  daily
    WHERE  processed =0
      AND  dateDay <= '2010-02-28'
    GROUP BY  DATE_FORMAT( dateDay, '%Y%m' ) ,
              customerid
    ORDER BY  dateDay ASC

This one needs
INDEX(processed, dateDay)

As a first cut on building an index...
1. list all fields in the WHERE clause that are controlled by '=' with a constant.
2. then list one more field from these (in this order)
2a. a "range" test in WHERE (<, >, IN, BETWEEN, ...)
2b. the first field in the GROUP BY
2c. the first field in the ORDER BY
I repeat; this is a 'first cut'. In your case, I got through 1 and 2a.

One more thing... Looking back at your original query -- you should have
INDEX(processed, customerid, dateDay) or
INDEX(customerid, processed, dateDay)
(Either would work almost equally well; neither would be optimal for your new query.)



Edited 1 time(s). Last edit at 03/17/2010 10:01PM by Rick James.