title: MySQL not responding for simple queries when using INNER JOIN. especially "Sending data" takes too much time
body: <p>I have two tables; <code>songs</code> with 48 959 281 rows, and <code>popular_songs</code> with 5 721 117  rows.</p>
  
  <p>This is structure of that two tables:</p>
  
  <pre><code>CREATE TABLE songs (
    songId       BIGINT(20)    NOT NULL,
    songName     VARCHAR(1000) NOT NULL,
    songDuration BIGINT(20)    NOT NULL,
    songPreview  VARCHAR(1000) NOT NULL,
    PRIMARY KEY (songId),
    INDEX (songDuration)
  )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    DEFAULT COLLATE utf8_general_ci;
  
  CREATE TABLE popular_songs (
    storeFrontId BIGINT(20) NOT NULL,
    genreId      BIGINT(20) NOT NULL,
    songId       BIGINT(20) NOT NULL,
    songRank     INT(11)    NOT NULL,
    INDEX (storeFrontId),
    INDEX (genreId),
    INDEX (songId),
    INDEX (songRank),
    CONSTRAINT popular_song UNIQUE (storeFrontId, genreId, songId),
    FOREIGN KEY (storeFrontId) REFERENCES storefront (storeFrontId),
    FOREIGN KEY (genreId) REFERENCES genre (genreId),
    FOREIGN KEY (songId) REFERENCES songs (songId)
  )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    DEFAULT COLLATE utf8_general_ci;
  </code></pre>
  
  <p>When I'm running this below two quires everything works fine.</p>
  
  <pre><code>mysql&gt; SELECT count(*) FROM songs;
  +----------+
  | count(*) |
  +----------+
  | 48959281 |
  +----------+
  1 row in set (9.10 sec)
  
  mysql&gt; SHOW PROFILE;
  +--------------------------------+----------+
  | Status                         | Duration |
  +--------------------------------+----------+
  | starting                       | 0.000008 |
  | Waiting for query cache lock   | 0.000002 |
  | checking query cache for query | 0.000019 |
  | checking permissions           | 0.000003 |
  | Opening tables                 | 0.000011 |
  | System lock                    | 0.000004 |
  | Waiting for query cache lock   | 0.000017 |
  | init                           | 0.000006 |
  | optimizing                     | 0.000003 |
  | statistics                     | 0.000004 |
  | preparing                      | 0.000004 |
  | executing                      | 0.000003 |
  | Sending data                   | 9.100444 |
  | end                            | 0.000012 |
  | query end                      | 0.000005 |
  | closing tables                 | 0.000008 |
  | freeing items                  | 0.000007 |
  | Waiting for query cache lock   | 0.000002 |
  | freeing items                  | 0.000012 |
  | Waiting for query cache lock   | 0.000002 |
  | freeing items                  | 0.000001 |
  | storing result in query cache  | 0.000002 |
  | logging slow query             | 0.000001 |
  | cleaning up                    | 0.000003 |
  +--------------------------------+----------+
  24 rows in set (0.00 sec)
  </code></pre>
  
  <hr>
  
  <pre><code>mysql&gt; SELECT count(*) FROM popular_songs;
  +----------+
  | count(*) |
  +----------+
  |  5721117 |
  +----------+
  1 row in set (1.34 sec)
  
  mysql&gt; SHOW PROFILE;
  +--------------------------------+----------+
  | Status                         | Duration |
  +--------------------------------+----------+
  | starting                       | 0.000012 |
  | Waiting for query cache lock   | 0.000004 |
  | checking query cache for query | 0.000037 |
  | checking permissions           | 0.000006 |
  | Opening tables                 | 0.000017 |
  | System lock                    | 0.000007 |
  | Waiting for query cache lock   | 0.000031 |
  | init                           | 0.000010 |
  | optimizing                     | 0.000017 |
  | statistics                     | 0.000004 |
  | preparing                      | 0.000004 |
  | executing                      | 0.000003 |
  | Sending data                   | 1.343991 |
  | end                            | 0.000010 |
  | query end                      | 0.000005 |
  | closing tables                 | 0.000007 |
  | freeing items                  | 0.000007 |
  | Waiting for query cache lock   | 0.000002 |
  | freeing items                  | 0.000011 |
  | Waiting for query cache lock   | 0.000001 |
  | freeing items                  | 0.000001 |
  | storing result in query cache  | 0.000002 |
  | logging slow query             | 0.000001 |
  | cleaning up                    | 0.000003 |
  +--------------------------------+----------+
  24 rows in set (0.00 sec)
  </code></pre>
  
  <p>When I'm running these below queries MySQL is not responding. I don't understand what the reason is.</p>
  
  <pre><code>SELECT COUNT(*)
  FROM songs
  INNER JOIN popular_songs ON popular_songs.songId = songs.songId
  </code></pre>
  
  <hr>
  
  <pre><code>SELECT COUNT(*)
  FROM songs
  INNER JOIN popular_songs ON popular_songs.songId = songs.songId
  WHERE songs.songDuration &gt; 0
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p>MySQL responded with above two queries. But it takes 250-300 seconds on <code>Sending data</code>. How to optimize this.</p>
  
  <p><strong>EXPLAIN EXTENDED report:</strong></p>
  
  <pre><code>mysql&gt; EXPLAIN EXTENDED
      -&gt; SELECT COUNT(*)
      -&gt; FROM songs
      -&gt; INNER JOIN popular_songs ON popular_songs.songId = songs.songId;
  +----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+
  | id | select_type | table         | type   | possible_keys | key      | key_len | ref                         | rows    | filtered | Extra       |
  +----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+
  |  1 | SIMPLE      | popular_songs | index  | songId        | songRank | 4       | NULL                        | 6449163 |   100.00 | Using index |
  |  1 | SIMPLE      | songs         | eq_ref | PRIMARY       | PRIMARY  | 8       | itunes.popular_songs.songId |       1 |   100.00 | Using index |
  +----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+
  2 rows in set, 1 warning (0.01 sec)
  
  mysql&gt; EXPLAIN EXTENDED
      -&gt; SELECT COUNT(*)
      -&gt; FROM songs
      -&gt; INNER JOIN popular_songs ON popular_songs.songId = songs.songId
      -&gt; WHERE songs.songDuration &gt; 0;
  +----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+
  | id | select_type | table         | type   | possible_keys        | key      | key_len | ref                         | rows    | filtered | Extra       |
  +----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+
  |  1 | SIMPLE      | popular_songs | index  | songId               | songRank | 4       | NULL                        | 6449163 |   100.00 | Using index |
  |  1 | SIMPLE      | songs         | eq_ref | PRIMARY,songDuration | PRIMARY  | 8       | itunes.popular_songs.songId |       1 |   100.00 | Using where |
  +----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+
  2 rows in set, 1 warning (0.00 sec)
  </code></pre>
  
  <p><strong>When using INDEX <code>songId</code> for joing two tables:</strong></p>
  
  <pre><code>mysql&gt; SELECT COUNT(*) FROM songs INNER JOIN popular_songs USE INDEX FOR JOIN (songId) ON popular_songs.songId = songs.songId;
  +----------+
  | COUNT(*) |
  +----------+
  |  5721117 |
  +----------+
  1 row in set (25.35 sec)
  
  mysql&gt; SHOW PROFILE;
  +--------------------------------+-----------+
  | Status                         | Duration  |
  +--------------------------------+-----------+
  | starting                       |  0.000011 |
  | Waiting for query cache lock   |  0.000004 |
  | checking query cache for query |  0.000045 |
  | checking permissions           |  0.000004 |
  | checking permissions           |  0.000004 |
  | Opening tables                 |  0.000023 |
  | System lock                    |  0.000007 |
  | Waiting for query cache lock   |  0.000030 |
  | init                           |  0.000021 |
  | optimizing                     |  0.000011 |
  | statistics                     |  0.000020 |
  | preparing                      |  0.000012 |
  | executing                      |  0.000005 |
  | Sending data                   | 25.350160 |
  | end                            |  0.000019 |
  | query end                      |  0.000005 |
  | closing tables                 |  0.000024 |
  | freeing items                  |  0.000022 |
  | Waiting for query cache lock   |  0.000002 |
  | freeing items                  |  0.000016 |
  | Waiting for query cache lock   |  0.000001 |
  | freeing items                  |  0.000001 |
  | storing result in query cache  |  0.000002 |
  | logging slow query             |  0.000001 |
  | logging slow query             |  0.000005 |
  | cleaning up                    |  0.000003 |
  +--------------------------------+-----------+
  26 rows in set (0.00 sec)
  </code></pre>
  
  <p>It's pretty nice. But, still it's take 25 seconds on <code>Sending data</code>.</p>
  
  <p><strong>Explain extended for above query:</strong></p>
  
  <pre><code>mysql&gt; EXPLAIN EXTENDED SELECT COUNT(*) FROM songs INNER JOIN popular_songs USE INDEX FOR JOIN (songId) ON popular_songs.songId = songs.songId;
  +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+
  | id | select_type | table         | type   | possible_keys | key     | key_len | ref                         | rows    | filtered | Extra       |
  +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+
  |  1 | SIMPLE      | popular_songs | index  | songId        | songId  | 8       | NULL                        | 2684407 |   100.00 | Using index |
  |  1 | SIMPLE      | songs         | eq_ref | PRIMARY       | PRIMARY | 8       | itunes.popular_songs.songId |       1 |   100.00 | Using index |
  +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+
  2 rows in set, 1 warning (0.00 sec)
  </code></pre>