Re: count distinct optimization + Specified key was too long error
> "Specified key was too long; max key length is 1000 bytes"
On the one hand, you are hitting a 'hard limit'. On the other hand, it is telling you "you are doing something wrong".
To make the query run faster, you would need
CREATE INDEX i on tbl ( a,b,c,d,e, col)
but that is even bigger, not not possible.
"Prefix" indexing won't help, since it needs the entire values of a,b,c,d,e.
Are they VARCHAR(255)? Do you _really_ need 255? Do a few SELECT MAX(LENGTH(a)) queries to see how long the columns really are. Perhaps ALTERing the TABLE to MODIFY COLUMN would make your technique work better.
Show us
SHOW TABLE STATUS LIKE 'tbl';
SHOW CREATE TABLE tbl;
EXPLAIN SELECT ...
There may be other workarounds. Some possibilities: PRIMARY KEY, order of fields, ENGINE being used, datatypes, summary tables, etc.