Jump to content

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


Garethp
 Share

Recommended Posts

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

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.