title: Optimize range query with group by
body: <p>Having trouble with a query. Here is the outline -</p>
  <p>Table structure:</p>
  <pre><code>CREATE TABLE `world` (
    `placeRef` int NOT NULL,
    `forenameRef` int NOT NULL,
    `surnameRef` int NOT NULL,
    `incidence` int NOT NULL
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;
  
  ALTER TABLE `world`
    ADD KEY `surnameRef_forenameRef` (`surnameRef`,`forenameRef`),
    ADD KEY `forenameRef_surnameRef` (`forenameRef`,`surnameRef`),
    ADD KEY `forenameRef` (`forenameRef`,`placeRef`);
  COMMIT;
  </code></pre>
  <p>This table contains data like and has over 600,000,000 rows:</p>
  <pre><code>placeRef    forenameRef    surnameRef    incidence
  1           1              2             100
  2           1              3             600
  </code></pre>
  <p>This represents the number of people with a given forename-surname combination in a place.</p>
  <p>I would like to be able to query all the forenames that a surname is attached to; and then perform another search for where those forenames exist, with a count of the sum incidence. For Example: get all the forenames of people who have the surname &quot;Smith&quot;; then get a list of all those forenames, grouped by place and with the sum incidence. I can do this with the following query:</p>
  <pre><code>SELECT placeRef, SUM( incidence )
  FROM world
  WHERE forenameRef IN
  (
      SELECT DISTINCT forenameRef
      FROM world
      WHERE surnameRef = 214488
  )
  GROUP BY world.placeRef
  </code></pre>
  <p>However, this query takes about a minute to execute and will take more time if the surname being searched for is common.</p>
  <p>The root problem is: performing a range query with a group doesn't utilize the full index.</p>
  <p>Any suggestions how the speed could be improved?</p>