why is my group by not using my index?

[gnznroses]

EXPLAIN SELECT campaign
FROM clicks
WHERE user = 123
GROUP BY campaign

 

I get "Using where; Using temporary; Using filesort"

(ref=const, key and possible keys=user, rows=hundreds of thousands)

 

campaign is an unsigned int - it is indexed (with a cardinality of about 2000)

user is an unsigned int - it is indexed also

 

this makes my query very slow. any way to fix this?

 

thanks

 

 

PS, I read this (http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html) but it's a bit hard to follow what they're saying.

[jskywalker]

Try adding user to your GROUP BY

This should 'tell' MySQL to use the index on user, and not use a 'filesort'

 

If this does not help, more info is needed, like

1) SHOW CREATE TABLE clicks

2) How many records are the in your table?

[gnznroses]

i still get the same result

EXPLAIN SELECT campaign
FROM clicks
WHERE user = 123
GROUP BY campaign, user

 

 

 

CREATE TABLE `clicks` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `campaign` smallint(10) unsigned NOT NULL,
  `user` mediumint( unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user` (`user`),
  KEY `datetime` (`datetime`),
  KEY `campaign` (`campaign`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=14629687 ;

[sader]

not sure this will help but here's what I have found change GROUP BY to FORCE INDEX FOR GROUP BY

 

[gnznroses]

i found the solution, sorta.

making a composite index on the two columns.

 

the problem is, the table actually has about a dozen columns, not just the ones i included in my code. and there's several different combinations of columns that we use in various queries (campaign+user, campaign+date, user+date, etc etc). so i would almost need multiple composite indexes...

 

so anytime you're selecting from one column (and using either distinct or group by) and filtering by another, it has to use a temporary table unless you have a composite index on both columns?

[fenway]

Yes, that's usually the case.