title: Mysql takes more time in sending data
body: <p>I have this particular query which takes long time to execute, other queries on the same tables execute very fast. Querycache is enabled in mysql but still the below query takes more than 80 seconds everytime and CPU crosses 100% utilization. </p>
  
  <p>I cannot modify the query because it is generated by Drupal. Is there anything else I can do to improve performance?</p>
  
  <p>The <b>query</b> is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select count(*) 
  from (
      SELECT slk.key_id AS key_id 
      FROM slk slk  
          LEFT JOIN users users ON    slk.uid = users.uid 
          LEFT JOIN node node_users ON users.uid = node_users.uid 
              AND   node_users.type = 'profile'
  ) count_alias;
  </code></pre>
  
  <p>Below is the <b>profile</b> information:</p>
  
  <pre class="lang-none prettyprint-override"><code>+--------------------------------+-----------+
  | Status                         | Duration  |
  +--------------------------------+-----------+
  | starting                       |  0.000029 | 
  | checking query cache for query |  0.000093 | 
  | Opening tables                 |  0.000210 | 
  | System lock                    |  0.000007 | 
  | Table lock                     |  0.000075 | 
  | optimizing                     |  0.000008 | 
  | statistics                     |  0.000113 | 
  | preparing                      |  0.000027 | 
  | executing                      |  0.000004 | 
  | Sending data                   | 66.086903 | 
  | init                           |  0.000027 | 
  | optimizing                     |  0.000009 | 
  | executing                      |  0.000018 | 
  | end                            |  0.000003 | 
  | query end                      |  0.000004 | 
  | freeing items                  |  0.000049 | 
  | storing result in query cache  |  0.000116 | 
  | removing tmp table             |  0.033162 | 
  | closing tables                 |  0.000106 | 
  | logging slow query             |  0.000003 | 
  | logging slow query             |  0.000085 | 
  | cleaning up                    |  0.000007 | 
  +--------------------------------+-----------+
  </code></pre>
  
  <p><b>explain</b> on the query gives:</p>
  
  <pre class="lang-none prettyprint-override"><code>| id | select_type | table      | type   | possible_keys         | key     | key_len | ref             | rows  | Extra                        |
  |  1 | PRIMARY     | NULL       | NULL   | NULL                  | NULL    | NULL    | NULL            |  NULL | Select tables optimized away | 
  |  2 | DERIVED     | slk        | ALL    | NULL                  | NULL    | NULL    | NULL            | 55862 |                              | 
  |  2 | DERIVED     | users      | eq_ref | PRIMARY               | PRIMARY | 4       | gscom.slk.uid   |     1 | Using index                  | 
  |  2 | DERIVED     | node_users | ref    | node_type,uid,idx_ctp | uid     | 4       | gscom.users.uid |     3 |                              | 
  </code></pre>
  
  <p><code>idx_ctp</code> is an index on (<code>uid</code>, <code>type</code>).</p>
  
  <p>The query cache is working and below are the stats.</p>
  
  <p><code>show variables like '%query_cache%';</code>:</p>
  
  <pre class="lang-none prettyprint-override"><code>| Variable_name                | Value    |
  | have_query_cache             | YES      | 
  | query_cache_limit            | 2097152  | 
  | query_cache_min_res_unit     | 4096     | 
  | query_cache_size             | 52428800 | 
  | query_cache_type             | ON       | 
  | query_cache_wlock_invalidate | OFF      |
  </code></pre>
  
  <p><code>mysql&gt; show status like '%Qcache%';</code>:</p>
  
  <pre class="lang-none prettyprint-override"><code>| Variable_name           | Value    |
  | Qcache_free_blocks      | 1255     | 
  | Qcache_free_memory      | 22902848 | 
  | Qcache_hits             | 1484908  | 
  | Qcache_inserts          | 1036344  | 
  | Qcache_lowmem_prunes    | 95086    | 
  | Qcache_not_cached       | 3975     | 
  | Qcache_queries_in_cache | 14271    | 
  | Qcache_total_blocks     | 30117    | 
  </code></pre>