MySQL partial indexes on varchar fields and group by optimization

I am having some issues with a group query with MySQL.

Question

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?

Details

My setup:

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

A number of columns and indexes are not shown here since they don't really impact the issue.

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:

SELECT COUNT(*) AS count_all, referrer_host AS referrer_host FROM `sessions` GROUP BY referrer_host;

The explain gives:

+----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra                        开发者_Python百科   |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | sessions | ALL  | NULL          | NULL | NULL    | NULL | 303049 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+

I have a partial index on referrer_host, but it isn't using it. Even if I try to USE INDEX or FORCE INDEX it doesn't help. The explain is the same, as is the performance.

If I add a full index on referrer_host, instead of a 10 character partial index, everything is works better, if not instantly. (350ms vs. 10 seconds)

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.

with the full index, the query will find scan the entire index and return the number of records pointed to for each unique key. the table isn't touched.

with the partial index, the engine doesn't know the value of the referrer_host until it looks at the record. It has to scan the whole table!

if most of the values for referrer_host are less than 10 chars then in theory, the optimiser could use the index and then only check rows that have more than 10 chars. But, because this is not a clustered index it would have to make many non-sequential disk reads to find these records. It could end up being even slower, because a table scan will at least be a sequential read. Instead of making assumptions, the optimiser just does a scan.

Try this query:

EXPLAIN SELECT COUNT(referrer_host) AS count_all, referrer_host  FROM `sessions` GROUP BY referrer_host;

Now the count will fail for the group by on referrer_host = null, but I'm uncertain if there's another way around this.

You're grouping on referrer_host for all the rows in the table. As your index doesn't include referrer_host (it contains the first 10 chars!), it's going to scan the whole table.

I'll bet that this is faster, though less detailed:

SELECT COUNT(*) AS count_all, substring(referrer_host,1,10) AS referrer_host FROM `sessions` GROUP BY referrer_host;

If you need the full referrer, index it.