title: Correct indexing by Join-Where-Group By select queries avoiding Using temporary; Using filesort
body: <p>I've searched a lot about a solution of the described below case, but unfortunately I haven't found a similar case.</p>
  
  <p>I have the following scenario:
  (as a new user the site rejected my picture, but I can send it via mail. Below is a textual representation of it)</p>
  
  <pre><code>Table 1 "swap_plan"          Table 2 "cell"
  ClusterName | SiteID         SiteID    | Cell      |  Time       | Counter
  -----------------------      ---------------------------------------------
  Cluster A   | SiteID A1      SiteID A1 | Cell A1-1 | day1        | 5
  Cluster A   | SiteID A2      SiteID A1 | Cell A1-1 | day2        | 3
  Cluster A   | SiteID A3      SiteID A1 | Cell A1-1 | day3        | 6
  Cluster A   | SiteID A4      SiteID A1 | Cell A1-2 | day1        | 6
  Cluster A   | SiteID A5      SiteID A1 | Cell A1-2 | day2        | 2
  Cluster A   | SiteID A6      SiteID A1 | Cell A1-2 | day3        | 9
  .......................      ..............................................
  Cluster B   | .........      ..............................................
  
  (Where No 1)      (ON Clause "SiteID")            (Where No 2)    Sum(Counter)
  </code></pre>
  
  <p>I have to display some performance indicators ("Counter" from table 2 "cell"), aggregated over time ("Time" from table 2 "cell") and cluster ("ClusterName" from table 1 "swap_plan").</p>
  
  <p>The join is done via the common column for both tables "SiteID". Please, note, that in Table 2 "cell" each SiteID consists of 3 different objects ("Cell"). So, in fact I do SUM() of "Counter" for each Cell.</p>
  
  <p>The query is following:</p>
  
  <pre><code>SELECT ClusterName,Time,SUM(counter)
  FROM cell
  INNER JOIN swap_plan ON swap_plan.Siteid = cell.Siteid
  WHERE ClusterName='Cluster A' AND Time&gt;=day1 AND Time&lt;=day2
  GROUP BY Time
  </code></pre>
  
  <p>Column Types are following:</p>
  
  <p>Table 1 "swap plan":</p>
  
  <ol>
  <li>ClusterName - CHAR(30)</li>
  <li>SiteID - VARCHAR(10)</li>
  </ol>
  
  <p>Table 2 "cell":</p>
  
  <ol>
  <li>SiteID - VARCHAR(10)</li>
  <li>Time - DATETIME</li>
  <li>Counter - INT</li>
  </ol>
  
  <p>"Explain" showed following:</p>
  
  <pre><code>table          type    key           key_len      ref               rows  Extra
  
  swap_plan      ref     Index 1       30           const             31    Using where; Using index; Using temporary; Using filesort
  cell           ref     Index_siteid  13           swap_plan.SiteID  368   Using where
  </code></pre>
  
  <p>The used indexes are following:</p>
  
  <p>swap_plan: Index 1 (1. ClusterName and 2. SiteID)</p>
  
  <p>cell: Index_siteid (SiteID)</p>
  
  <p>The number of rows, in which the optimizer looks is rathet low, which is good:</p>
  
  <p>swap_plan: 31 out of 6066 and cell: 368 out of 6.6 mil.</p>
  
  <p>My problem is these "Using temporary; Using filesort". As far as I understand this comes from the sorting needed for Group By (If I remove it, these processes are not executed according to Explain). I found that in order to avoid them you need to have an index on the columns by which you group by. I have a special index including only "Time" column, but this one is not used, even with a hint "USE INDEX FOR GROUP BY ()".</p>
  
  <p>As a result my query runs not sufficiently fast - it takes about 15 seconds (for let's say 15 SiteIDs and 10 dates) and I need to reduce this duration to at least half of it.</p>
  
  <p><strong>My main questions are:</strong></p>
  
  <ul>
  <li>Is is possible at all to remove "Using temporary; Using filesort" or
     to reduce the needed time for their execution? (I tried to increase
     Read Buffer Size to 16MB, without effect)</li>
  <li>What kind of index definitions I need in JOIN situations, when in WHERE clause I filter by 2 columns in different tables and in ON clause I filter by a 3rd column</li>
  <li>What kind of Group By optimization I can apply (indexing, etc.)?</li>
  </ul>
  
  <p>Thank you very much in advance!</p>