Tag system for reviews a little to slow

[poonone]

So, I've been fighting with this one for quite some time.  It comes up every time in my slow query log so I want to fix it.

 

I'd simply like to make this more efficient so it won't show up in the slow queries log.  Seems that the temporary tables and filesorts are not good.

Basically it needs to select all of the tags in the customerReviewTags table and their count so they can display like "Snazzy (12)" where "Snazzy" is the tag name and "(12)" is the count of times someone has picked that tag.  The problem is compounded by the fact that I also need to get all the tags from another table (custReviewCatTagMap) that contains default tags for a category so the user can select from those also.  That is the second table that is UNIONed to the first (on that one we just set the count to (0))

 

I'm using MySQL 5.1.30 Community

 

Here is the explain:

1	PRIMARY	<derived2>	ALL		NULL		NULL	NULL	NULL		4	Using temporary; Using filesort
2	DERIVED	r		ref		PRIMARY,f0,f3	f0	12			1	Using index; Using temporary; Using filesort
2	DERIVED	tm		ref		f1,f0		f0	8	r.id		16	
2	DERIVED	t		eq_ref		PRIMARY,f2	PRIMARY	8	tm.tagID	1	Using where
3	UNION	tm		ref		f1,f0		f0	8			7	
3	UNION	t		eq_ref		PRIMARY,f2	PRIMARY	8	tm.tagID	1	Using where
UNION RESULT		<union2,3>	ALL						

 

 

SELECT *, MAX(tCount) AS mTCnt 
FROM 
((SELECT t.id, t.tagName, t.tagType, COUNT(t.id) AS tCount 
FROM custReviewTagMap tm 
INNER JOIN custReviewTags t ON tm.tagID = t.id 
INNER JOIN custProdReview r ON r.id = tm.reviewID 
WHERE r.productID = 333222346774 
AND t.approved = 1 
AND r.approved = 1 
AND t.tagType = 3 GROUP BY t.id) 
UNION 
(SELECT t.id, t.tagName, t.tagType, (0) AS tCount 
FROM custReviewCatTagMap tm 
INNER JOIN custReviewTags t ON tm.tagID = t.id 
WHERE tm.catID = '119' 
AND t.approved = 1 
AND t.tagType = 3)) AS b 
GROUP BY id 
ORDER BY mTCnt DESC LIMIT 25;

[fenway]

First, make sure the inner tables (before the union) execute as you expect.  Second, consider summarizing this data, instead of pulling it in real-time.

[poonone]

Could you please expand on what you mean by summarize? 

 

The inner tables do not actually run that well on their own.  Super odd because I have probably over a thousand other queries on this site that all run very quick and this one is killing me.  Shows up every time and it's in every page of the site.

[fenway]

Well, then EXPLAIN the inner queries.

[poonone]

So here are the internal EXPLAINs.  They I guess I don't know what to look for since they don't look horrible to me.  The first takes somewhere around: 1.14s to execute and the second is about 1.4s

 

EXPLAIN:

SELECT t.id, t.tagName, t.tagType, (0) AS tCount FROM custReviewCatTagMap tm INNER JOIN custReviewTags t ON tm.tagID = t.id WHERE tm.catID = '416' AND t.approved = 1 AND t.tagType = 3;

 

1	SIMPLE	tm	ref		f1,f0		f0		8		const			1	
1	SIMPLE	t	eq_ref		PRIMARY,f2	PRIMARY		8		scottmulder.tm.tagID	1	Using where

 

EXPLAIN:

SELECT t.id, t.tagName, t.tagType, COUNT(t.id) AS tCount FROM custReviewTagMap tm INNER JOIN custReviewTags t ON tm.tagID = t.id INNER JOIN custProdReview r ON r.id = tm.reviewID WHERE r.productID = 333222342703 AND t.approved = 1 AND r.approved = 1 AND t.tagType = 3 GROUP BY t.id;

 

1	SIMPLE	r	ref		PRIMARY,f0,f3	f0		12	const,const			1	Using index; Using temporary; Using filesort
1	SIMPLE	tm	ref		f1,f0		f0		8	scottmulder.r.id		15
1	SIMPLE	t	eq_ref		PRIMARY,f2	PRIMARY		8	scottmulder.tm.tagID		1	Using where

[poonone]

Couldn't edit the above post, but wanted to point out that there are not many rows in any of the tables that are in the query.

 

custProdReview Table has 117 rows

custReviewCatTagMap has 350

custReviewTagMap has 556

custReviewTags has 242

 

All primary keys are Type bigint with a length of 20

All of the keys that are listed in EXPLAIN of the main query are of type bigint with a length of 20

 

And since I didn't know how to do this before:

 

CREATE TABLE `custProdReview` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `review` text NOT NULL,

  `title` varchar(255) NOT NULL,

  `wouldRecomend` int(1) NOT NULL DEFAULT '1',

  `summary` varchar(128) NOT NULL,

  `userID` int(11) NOT NULL,

  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `productID` bigint(12) NOT NULL,

  `rating` int(1) NOT NULL DEFAULT '5',

  `votes` int(3) NOT NULL DEFAULT '1',

  `denied` int(1) NOT NULL DEFAULT '0',

  `approved` int(1) NOT NULL DEFAULT '0',

  `ip` varchar(15) NOT NULL DEFAULT '0.0.0.0',

  PRIMARY KEY (`id`),

  KEY `f1` (`userID`),

  KEY `f0` (`productID`,`approved`,`id`),

  KEY `f3` (`id`,`productID`,`approved`),

  FULLTEXT KEY `f2` (`title`,`review`)

) ENGINE=MyISAM AUTO_INCREMENT=236 DEFAULT CHARSET=latin1

 

 

 

CREATE TABLE `custReviewTags` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `tagName` varchar(20) NOT NULL DEFAULT 'NULL',

  `tagType` int(2) NOT NULL,

  `approved` int(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  UNIQUE KEY `f1` (`tagName`,`tagType`),

  KEY `f2` (`id`,`approved`,`tagType`),

  KEY `f0` (`tagName`)

) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=latin1

 

 

CREATE TABLE `custReviewTagMap` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `tagID` bigint(20) NOT NULL,

  `reviewID` bigint(20) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `f1` (`tagID`,`reviewID`),

  KEY `f0` (`reviewID`)

) ENGINE=MyISAM AUTO_INCREMENT=696 DEFAULT CHARSET=latin1

 

 

CREATE TABLE `custReviewCatTagMap` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `tagID` bigint(20) NOT NULL,

  `catID` bigint(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `f1` (`tagID`,`catID`),

  KEY `f0` (`catID`)

) ENGINE=MyISAM AUTO_INCREMENT=355 DEFAULT CHARSET=latin1

[poonone]

Just re-igniting this... Any ideas?  Any help is much appreciated as I'm still at a loss on this one.

 

Thanks in advance,

Scott

[fenway]

That sounds wrong... 1s to query a table with const & PRIMARY on small record sets?