查询
SELECT * FROM ProductReviews
INNER JOIN RatingActions USING(RatingActionID)
LEFT JOIN ProductRatingVotes USING(RatingActionID)
WHERE ProductReviews.ProductID="200129" AND ProductReviewStatus="1"
ORDER BY RatingActionTimestamp DESC;
执行计划
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ProductReviews
type: ref
possible_keys: FK_ProductReview_ProductID,FK_ProductReviews_RatingActionID
key: FK_ProductReview_ProductID
key_len: 4
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ProductRatingVotes
type: ref
possible_keys: FK_ProductRatingVotes_RatingActionID
key: FK_ProductRatingVotes_RatingActionID
key_len: 4
ref: scart.ProductReviews.RatingActionID
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: RatingActions
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: scart.ProductReviews.RatingActionID
rows: 1
Extra:
3 rows in set (0.00 sec)
虽然它只扫描一行,但using temporary
杀死了它,需要3-4秒才能完成(非常繁忙的服务器;在我的本地主机上,0.004秒,这仍然比没有排序的版本慢6倍以上。
据我所知,using temporary
是由于order by
列不在第一个表中造成的。
更新表:
CREATE TABLE `ProductReviews` (
`ProductReviewID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ProductID` int(10) unsigned NOT NULL DEFAULT '0',
`RatingActionID` int(10) unsigned NOT NULL DEFAULT '0',
`ProductReviewText` text NOT NULL,
`ProductReviewStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ProductReviewID`),
KEY `FK_ProductReview_ProductID` (`ProductID`),
KEY `FK_ProductReviews_RatingActionID` (`RatingActionID`),
CONSTRAINT `FK_ProductReviews_RatingActionID` FOREIGN KEY (`RatingActionID`) REFERENCES `ratingactions` (`RatingActionID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_ProductReview_ProductID` FOREIGN KEY (`ProductID`) REFERENCES `products` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8
CREATE TABLE `ratingactions` (
`RatingActionID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`RatingActionTimestamp` int(10) unsigned NOT NULL DEFAULT '0',
`CustomerID` int(10) unsigned DEFAULT NULL,
`RatingActionIPAddress` int(10) unsigned NOT NULL DEFAULT '0',
`RatingActionInputName` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`RatingActionID`),
KEY `FK_RatingActions_CustomerID` (`CustomerID`),
CONSTRAINT `FK_RatingActions_CustomerID` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8
CREATE TABLE `ProductRatingVotes` (
`ProductRatingVoteID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ProductID` int(10) unsigned NOT NULL DEFAULT '0',
`RatingActionID` int(10) unsigned NOT NULL DEFAULT '0',
`ProductRatingVoteValue` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ProductRatingVoteStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ProductRatingVoteID`),
KEY `FK_ProductRatingVotes_ProductID` (`ProductID`),
KEY `FK_ProductRatingVotes_RatingActionID` (`RatingActionID`),
CONSTRAINT `FK_ProductRatingVotes_ProductID` FOREIGN KEY (`ProductID`) REFERENCES
`products` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_ProductRatingVotes_RatingActionID` FOREIGN KEY
(`RatingActionID`) REFERENCES `ratingactions` (`RatingActionID`) ON
DELETE CASCADE ON UPDATE CASCADE )
ENGINE=InnoDB AUTO_INCREMENT=142
DEFAULT CHARSET=utf8
试试这个:
SELECT /*STRAIGHT_JOIN*/ ProductReviews.ProductID, RatingActionTimestamp
FROM ratingactions
join ProductReviews USING(RatingActionID)
LEFT JOIN ProductRatingVotes USING(RatingActionID)
WHERE ProductReviews.ProductID=200129 AND ProductReviewStatus=1
order by RatingActionTimestamp desc;
这应该消除使用临时和使用文件排序的额外步骤。如果没有,请尝试取消注释。
与所有其他明显的事情不工作,然后我将提供以下…将产品评论移动到from的第一个位置,作为选择/from本身…然后应用join
SELECT STRAIGHT_JOIN
PR.ProductID,
RA.RatingActionTimestamp
FROM
( select PR1.ProductID,
PR1.RatingActionID
FROM ProductReviews PR1
WHERE
PR1.ProductID = 200129
AND PR1.ProductReviewStatus = 1 ) PR
JOIN rating actions RA
on PR.RatingActionID = RA.RatingActionID
LEFT JOIN ProductRatingVotes PRV
on PR.RatingActionID = PRV.RatingActionID
order by
RA.RatingActionTimestamp desc;
这样,它应该从内部查询开始,得到您期望的单行,然后首先连接到评级操作.product reviews,获得所讨论的不同产品的合格条目,然后继续连接到评级操作上的评级和评级投票表…
似乎其中一个表包含TEXT或BLOB字段,ORDER BY被迫使用磁盘进行排序。一种解决方案可能是在子查询中不使用字段进行排序,并再次与其他列连接。