title: MySQL GROUP BY on large tables
body: <p>I have a table with over 75 millions registers. I want to run a group by to summarize this registries.</p>
  
  <p>The table structure is:</p>
  
  <pre><code>CREATE TABLE `output_medicos_full` (
    `name` varchar(100) NOT NULL DEFAULT '',
    `term` varchar(50) NOT NULL DEFAULT '',
    `hash` varchar(40) NOT NULL DEFAULT '',
    `url` varchar(2000) DEFAULT NULL,
    PRIMARY KEY (`name`,`term`,`hash`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>I want execute the query bellow, but is taking so long using a dedicate mysql server 5.5 with 4GB RAM:</p>
  
  <pre><code>INSERT INTO TABLE report
  SELECT 
      `hash`
      ,CASE UPPER(SUBSTRING_INDEX(url, ':', 1)) 
          WHEN 'HTTP' THEN 1
          WHEN 'HTTPS' THEN 2
          WHEN 'FTP' THEN 3
          WHEN 'FTPS' THEN 4
          ELSE 0 end
      ,url
  FROM output_medicos_full
  GROUP BY `hash`;
  </code></pre>
  
  <p>On table report there is an unique index on hash column</p>
  
  <p>Any help to speed it up?</p>
  
  <p>Thank's</p>