CODEWITHSUNDEEP
mysqlquery-optimization
Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

MySQL - optimize "Using temporary" caused by ORDER BY

Here is the query

SELECT * FROM ProductReviews
INNER JOIN RatingActions USING(RatingActionID)
LEFT JOIN ProductRatingVotes USING(RatingActionID)
WHERE ProductReviews.ProductID="200129" AND ProductReviewStatus="1"
ORDER BY RatingActionTimestamp DESC;

Here is the execution plan

*************************** 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)

Although it's scanning exactly one row, the using temporary kills it and it takes 3-4 seconds to complete (a very busy server; on my localhost 0.004 seconds, which still is more than 6 times slower compared to version without order by).

As I understand, the using temporary is caused by the fact, that the order by column is not in the first table.

Is there a way to optimize this query, or should I duplicate the Timestamp into the ProductReviews table?

UPDATE Tables:

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

Upvotes: 1

Views: 1062

Answers (3)

DRapp
DRapp

Reputation: 48139

with all the other apparent things that are not working, I would then offer the following... Move the Product Reviews to the FIRST POSITION in the from as a select/from of itself... THEN apply the joins

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;

This way, it should START with the inner query resulting with the single row you expect, THEN join to rating action .product reviews FIRST, get those qualified entries for the distinct product in question, THEN continue the join out to the ratings and rating votes tables on the rating actions...

Upvotes: 1

ggiroux
ggiroux

Reputation: 6724

Try this instead:

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;

This should eliminate the using temporary and using filesort extra steps. If it doesn't, try uncommenting the STRAIGHT_JOIN.

Upvotes: 1

newtover
newtover

Reputation: 32094

It seems that one of the tables contains TEXT or BLOB fields and the ORDER BY is forced to use disk for sorting. A solution might be to sort without the fields in a subquery and join with the rest of columns once again.

Upvotes: 0

Related Questions