title: Why is Primary Key faster than Index?
body: <p>I have a MySQL 5.5 table that looks like this;</p>
  
  <pre><code>CREATE TABLE `date_test` (
    `id` int(11) NOT NULL DEFAULT '0',
    `report_date` datetime NOT NULL,
    `module_id` int(11) NOT NULL,
    `country_id` int(11) NOT NULL,
    `clicks` int(11) DEFAULT '0'
    PRIMARY KEY (`id`,`report_date`,`module_id`,`country_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>It only has one PK, and no Indexes. Running a simple SELECT query;</p>
  
  <pre><code>select count(*) from date_test
  where report_date between'2014-09-01' and '2014-09-15'
  </code></pre>
  
  <p>This takes <strong>1.1 seconds</strong>, returns 809,349 records.</p>
  
  <p>Now, I add an Index on report_date, to try and speed this up;</p>
  
  <pre><code>ALTER TABLE `date_test`
  ADD INDEX `report_date` (`report_date` ASC);
  </code></pre>
  
  <p>My select count(*) query now takes <strong>2.6 seconds</strong> to run. My question; how did adding this field as an Index impact the query performance?</p>
  
  <p>Adding EXPLAIN;</p>
  
  <pre><code>With Index and PK (2.6 seconds) 
  id            1   
  select_type   SIMPLE    
  table         date_test   
  type          range   
  possible_keys report_date   
  key           report_date   
  key_len       8   
  ref     
  rows          792170    
  Extra         Using where; Using index    
  
  With PK only, no Index (1.1 seconds)  
  id            1   
  select_type   SIMPLE    
  table         date_test   
  type          index   
  possible_keys     
  key           PRIMARY   
  key_len       1048    
  ref     
  rows          1584341   
  Extra         Using where; Using index    
  
  With no Index or PK (1.4 seconds)     
  id            1   
  select_type   SIMPLE    
  table         date_test   
  type          ALL   
  possible_keys     
  key     
  key_len     
  ref     
  rows          1652883   
  Extra         Using where   
  </code></pre>