Topic: 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:

Code: [Select]

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


Code: [Select]

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;

Code: [Select]

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;

Code: [Select]

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?