title: How do I improve performance for same table join? Somehow 1 index is not used
body: <p>I have a table for start and end year for each subscription payment.</p>
  <p>There are about 45,000 records.</p>
  <pre><code>CREATE TABLE `a_payment` (
    `user_id` int(11) NOT NULL,
    `pay_id` int(11) NOT NULL,
    `start_year` int(11) DEFAULT NULL,
    `end_year` int(11) DEFAULT NULL,
    PRIMARY KEY (`pay_id`),
    KEY `end_year` (`end_year`),
    KEY `start_year` (`start_year`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  </code></pre>
  <p>I want to find the renewal count for the year that the subscription expires.</p>
  <p>It reduces 45,000 records to 10 records, 1 for each year.</p>
  <pre><code>select  t1.end_year as year
          , 0 as total
          , count(t1.user_id) as renew
  from    a_payment t1
      , a_payment t2
  where   t1.user_id = t2.user_id
  and t1.end_year = t2.start_year
  group by t1.end_year
  order by t1.end_year
  </code></pre>
  <p>It takes 2 min 58 seconds.</p>
  <p>The &quot;sending data&quot; takes 178 seconds, 99.99962%.</p>
  <p>DESCRIBE the select statement shows</p>
  <ol>
  <li>select type simple, table t1, type all, key null, rows 39267, extra using where; using temporary; using filesort</li>
  <li>select type simple, table t2, type ref, key start_year, row t1.end_year, rows 7557, using where</li>
  </ol>
  <p>Why is it taking so long?  How come the end_year index is not used?  What do I need to change to make it faster?</p>