title: Why so slow this select query in mysql?
body: <p><strong>Table Schema</strong><br></p>
  
  <p>database engine is <strong>MyISAM</strong></p>
  
  <p><strong>commits</strong> (about 500,000,000 rows)
  <br>id (int) - PK (indexed)
  <br>project_id (int) - FK [project->id] (indexed)
  <br>committer_id - FK [user->id] (indexed)
  <br>... </p>
  
  <p><strong>projects</strong> (about 32,000,000 rows)
  <BR> id (int) - PK (indexed)
  <br>... </p>
  
  <p><strong>user</strong> (about 12,000,000 rows)
  <br> id - PK (indexed)
  <br>... </p>
  
  <hr>
  
  <p><strong>Explain</strong></p>
  
  <pre><code>SELECT COUNT(*) 
  FROM commits 
  WHERE committer_id = 30351173
  </code></pre>
  
  <p>above query is complete in <strong>10 sec.</strong></p>
  
  <pre><code>SELECT project_id , COUNT(*) as commit_count
  FROM commits
  WHERE committer_id = 30351173
  GROUP BY project_id
  </code></pre>
  
  <p>but, above query is not complete in <strong>2 hours</strong>(7,200 sec)</p>
  
  <p><strong><code>project_id</code></strong> in <code>commits</code> is complete indexing column, but the speed is too slow.</p>
  
  <p>Why is this happening?</p>
  
  <p><strong>Q) How do I speed up the second query?</strong></p>
  
  <hr>
  
  <p><strong>Additional</strong></p>
  
  <pre><code>SELECT project_id , COUNT(*) AS commit_count
  FROM commits
  WHERE committer_id = 1891264
  GROUP BY project_id
  </code></pre>
  
  <p>I query another <code>commiter_id</code> is complete <strong>15 sec.</strong></p>
  
  <p><strong>Additional - 2</strong></p>
  
  <pre><code>EXPLAIN SELECT COUNT(*) FROM commits WHERE committer_id = 30351173
  </code></pre>
  
  <p>output</p>
  
  <p><a href="https://i.stack.imgur.com/bDVJK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bDVJK.png" alt="enter image description here"></a></p>
  
  <pre><code>EXPLAIN SELECT project_id , COUNT(*) AS commit_count
  FROM commits
  WHERE committer_id = 30351173
  GROUP BY project_id
  </code></pre>
  
  <p>output</p>
  
  <p><a href="https://i.stack.imgur.com/0wKMZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0wKMZ.png" alt="enter image description here"></a></p>