MySQL Forums
Forum List  »  Optimizer & Parser

Re: Problems with query not using expeced indexes
Posted by: Rick James
Date: March 17, 2010 09:57PM

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' ) ,
    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.

Options: ReplyQuote

Written By
Re: Problems with query not using expeced indexes
March 17, 2010 09:57PM

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.