Mysql Query Improve Performance

Question

i have the following Query:

SELECT COUNT(sid),fDate,COUNT(DISTINCT(cid)) 
    FROM forwarding  
    WHERE fDate BETWEEN "2011-06-01" AND "2011-06-30" 
    GROUP BY fDate

Explain gives me the following output:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  forwarding  index   fDate,fDate_2   fDate_2 3       1481127 Using where

You can see that there is a lot of data. The total working Time is 12seconds. How can i improve the performance? I don't know what i can do more as setting the index.

There are my indices for this table:

fDate (fDate, f_shop)
fDate2(dDate),
f_shop(f_shop)

Thank you for your help.

UPDATE:

Now i added a column in my where clause and the query is much slower then before.

SELECT COUNT(sid),fDate,COUNT(DISTINCT(cid)) FROM forwarding 
WHERE fDate BETWEEN "2011-06-01" AND "2011-06-30"  AND f_shop=10077 GROUP BY fDate

I have an index on forwardDate and f_shop but the performance is to slow. What is the perfect solution? Thank you

Answer 1

In addition to ypercube's offered answer, your query where you are looking for a specific shop... I would have an index on

(f_shop, fdate, cid) to ensure all 3 parts of the index are utilized with the smallest WHERE clause qualifier in front.. in your last sample, you've included interest in a specific shop...

SELECT 
      COUNT(sid),
      fDate,
      COUNT(DISTINCT(cid)) 
   FROM 
      forwarding 
   WHERE 
          f_shop=10077 
      AND fDate BETWEEN "2011-06-01" AND "2011-06-30"
   GROUP BY 
      fDate

Answer 2

How MySQL Uses Indexes answered all my questions concerning MySQL indexes so far. It might be worth a read.