title: Indexing with GROUP BY, ORDER BY, and GROUP_CONCAT
body: <p><strong>SOLVED</strong> SEE BELOW</p>
  
  <p>I am trying to use both <code>GROUP BY</code> and <code>ORDER BY</code> in my query where I retrieve data sorted by difficulty. I have to use the <code>GROUP BY</code> because of the <code>GROUP CONCAT</code> since some tables such as the 'lookup_peripheral', link multiple values to the same key (content_id). I understand why MYSQL cannot use a index when performing this task since the GROUP BY and ORDER BY statements do not share the same field. However, I am looking for alternative solutions that won't require a day to retrieve the results. </p>
  
  <p>If I omit either the <code>GROUP BY</code> or <code>ORDER BY</code> clause, then the database uses an index, but the results lack either all of the peripherals or are not sorted by difficulty. </p>
  
  <p>I am using the 'lookup_difficulty' table in the <code>FROM</code> so I can use that index in ordering the results. The <code>lookup_xxxxx</code> tables store each allowed value and then the other tables such as <code>peripheral</code> link the submission to the value via the content_id. Everything is referenced to the submission <code>content_id</code>. The <code>content</code> table holds essential info such as member id, name, etc.  </p>
  
  <p>I apologize if my post is not clear enough.  </p>
  
  <pre><code>mysql&gt; describe peripheral;
  +------------------+----------+------+-----+---------+-------+
  | Field            | Type     | Null | Key | Default | Extra |
  +------------------+----------+------+-----+---------+-------+
  | peripheral_id    | int(2)   | NO   | PRI | NULL    |       |
  | peripheral       | char(30) | NO   |     | NULL    |       |
  | peripheral_total | int(5)   | NO   |     | NULL    |       |
  +------------------+----------+------+-----+---------+-------+
  
  mysql&gt; select * from peripheral;
  +---------------+-----------------+------------------+
  | peripheral_id | peripheral      | peripheral_total |
  +---------------+-----------------+------------------+
  |             1 | periph 1        |                0 |
  |             2 | periph 2        |                1 |
  |             3 | periph 3        |                3 |
  +---------------+-----------------+------------------+
  </code></pre>
  
  <p>:</p>
  
  <pre><code>mysql&gt; describe lookup_peripheral;
  +---------------+---------+------+------+---------+-------+
  | Field         | Type    | Null | Key  | Default | Extra |
  +---------------+---------+------+------+---------+-------+
  | content_id    | int(10) | NO   | INDEX| NULL    |       |
  | peripheral_id | int(2)  | NO   |      | NULL    |       |
  +---------------+---------+------+------+---------+-------+  
  
  
  mysql&gt; mysql&gt; select * from lookup_peripheral;
  +------------+---------------+
  | content_id | peripheral_id |
  +------------+---------------+
  |         74 |             2 |
  |         74 |             5 |
  |         75 |             2 |
  |         75 |             5 |
  |         76 |             3 |
  |         76 |             4 |
  +------------+---------------+
  </code></pre>
  
  <p></p>
  
  <p>The following is not using an index on lookup_difficulty, but rather a table sort and temporary table.</p>
  
  <pre><code>SELECT group_concat(DISTINCT peripheral.peripheral) as peripheral, content.member, .....
  FROM (lookup_difficulty)
  LEFT OUTER JOIN lookup_peripheral ON lookup_difficulty.content_id = lookup_peripheral.content_id
  LEFT OUTER JOIN peripheral ON peripheral.peripheral_id = lookup_peripheral.peripheral_id
  .....
  LEFT OUTER JOIN programmer ON programmer.programmer_id = lookup_programmer.programmer_id
  LEFT OUTER JOIN lookup_programming_language ON lookup_difficulty.content_id = lookup_programming_language.content_id
  
  GROUP BY lookup_difficulty.content_id
  ORDER BY lookup_dfficulty.difficulty_id
  LIMIT 30    
  </code></pre>
  
  <p>The ultimate goal is to retrieve results sorted by difficulty with the correct peripherals attached. I think I need a sub-query to achieve this.</p>
  
  <hr>
  
  <p><strong>EDIT: ANSWER BELOW:</strong></p>
  
  <p>Figured it out. I did what I suscpected I had to do, which was to add a sub-query. Since MYSQL can only use one index per table, I was unable to <code>GROUP BY</code> and <code>SORT BY</code> together for my particular setup. Instead, I added another query that would use another index on a different table to group the peripherals together. Here what I added in the <code>SELECT</code> statement above:</p>
  
  <pre><code>(SELECT group_concat(DISTINCT peripheral.peripheral) as peripheral
  FROM lookup_peripheral
  LEFT OUTER JOIN peripheral ON peripheral.peripheral_id = lookup_peripheral.peripheral_id
  WHERE lookup_difficulty.content_id = lookup_peripheral.content_id
  GROUP BY lookup_peripheral.content_id
  LIMIT 1) as peripheral
  </code></pre>
  
  <p>I used a <code>LEFT OUTER</code> since some entries do not have any peripherals. Total query time is now .02s on a 400MHz processor with 128MB of 100Hz RAM for a 40k row database for most of the tables. </p>
  
  <p><code>EXPLAIN</code> now gives me a <code>USING INDEX</code> for the <code>lookup_difficulty</code> table. I added this to achieve that:</p>
  
  <pre><code>ALTER TABLE `pictuts`.`lookup_difficulty` DROP PRIMARY KEY ,
  ADD PRIMARY KEY ( `difficulty_id` , `content_id` ) 
  </code></pre>
  
  <hr>
  
  <p><strong>Edit 2</strong>
  I noticed that with large offsets by using pagination, the page will load considerably slower. You may have experienced this with other sites as well. Fortuatly, there is a way to avoid this as pointed out by <a href="http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/" rel="nofollow">Peter Zaitsev</a>. Here is my updated snippet to achieve the same timings for offsets of 30K or 0:</p>
  
  <pre><code>FROM 
  SELECT lookup_difficulty.content_id, lookup_difficulty.difficulty_id
  FROM lookup_difficulty
  LIMIT '.$offset.', '.$per_page.'
  ) ld
  </code></pre>
  
  <p>Now just add <code>ld.whatever</code> to every <code>JOIN</code> made and there you have it! My query look like a total mess now, but at least it is optimized. I don't think anyone will make it this far in reading this...</p>