MySQL Query Examining 2M rows (Help please?) [Explain and Show Create inside]

[Garethp]

So, one of the sites I'm maintaining has a few very bad queries, and I'm just trying to track down what's the worst. Anyway, here's one of the queries that I could really use some help with, please. I've tried adding a key on hit_url_item, and hit_url_content, but no luck in making it use indexes. What can I do to make this query more efficient?

 

SELECT hit_name, COUNT( hit_id ) AS hits, CONCAT_WS(  '/',  `hit_url_content` ,  `hit_url_item` ) AS hit_url
FROM sc_hits
WHERE hit_url_content =  'articles/products/furniture'
GROUP BY hit_url_item
ORDER BY hits DESC 
LIMIT 5

 

Explained

 

id	select_type	table	type		possible_keys		key		key_len		ref	rows		Extra
1	SIMPLE		sc_hits	index		hit_url_content		hit_url_item	767		NULL	1994330		Using where; Using temporary; Using filesort

 

SHOW CREATE TABLE

 

CREATE TABLE `sc_hits` (
`hit_id` int(11) NOT NULL AUTO_INCREMENT,
`hit_url_content` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`hit_url_item` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`hit_name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
`hit_date` datetime NOT NULL,
PRIMARY KEY (`hit_id`),
KEY `hit_url_content` (`hit_url_content`),
KEY `hit_url_item` (`hit_url_item`),
KEY `hit_date` (`hit_date`)
) ENGINE=MyISAM AUTO_INCREMENT=14838361 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[Garethp]

Edit: Sorry, I don't know how to edit the main post, but I forgot my MySQL version. It's 5.1.61

[gizmola]

DROP your hit_url_content key and hit_url_item key and replace them a concatenated key of (hit_url_content, hit_url_item) and try the explain again.  Does it change? 

[fenway]

Also, I'm not sure that GROUP BY makes any sense given the columns in your SELECT list.