title: MySQL partial indexes on varchar fields and group by optimization
body: <p>I am having some issues with a group query with MySQL.  </p>
  
  <p><strong>Question</strong></p>
  
  <p>Is there a reason why a query won't use a 10 character partial index on a varchar(255) field to optimize a group by?</p>
  
  <p><strong>Details</strong></p>
  
  <p>My setup:</p>
  
  <pre><code>CREATE TABLE `sessions` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `ref_source` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `guid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `initial_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `referrer_host` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `campaign` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_sessions_on_user_id` (`user_id`),
    KEY `index_sessions_on_referrer_host` (`referrer_host`(10)),
    KEY `index_sessions_on_initial_path` (`initial_path`(10)),
    KEY `index_sessions_on_campaign` (`campaign`(10))
  ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  </code></pre>
  
  <p>A number of columns and indexes are not shown here since they don't really impact the issue.</p>
  
  <p>What I want to do is run a query to see all of the referring hosts and the number of session coming from each.  I don't have a huge table, but it is big enough where I full table scans aren't fun.  The query I want to run is:</p>
  
  <pre><code>SELECT COUNT(*) AS count_all, referrer_host AS referrer_host FROM `sessions` GROUP BY referrer_host;
  </code></pre>
  
  <p>The explain gives:</p>
  
  <pre><code>+----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
  | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
  +----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
  |  1 | SIMPLE      | sessions | ALL  | NULL          | NULL | NULL    | NULL | 303049 | Using temporary; Using filesort |
  +----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
  </code></pre>
  
  <p>I have a partial index on <code>referrer_host</code>, but it isn't using it.  Even if I try to <code>USE INDEX</code> or <code>FORCE INDEX</code> it doesn't help.  The explain is the same, as is the performance.</p>
  
  <p>If I add a full index on <code>referrer_host</code>, instead of a 10 character partial index, everything is works better, if not instantly. (350ms vs. 10 seconds)</p>
  
  <p>I have tested partial indexes that are bigger than the longest entry in the field to no avail as well.  The full index is the only thing that seems to work.</p>