Re: Slow query in cluster but not outside

[Alex Llera]

Stewart Smith Wrote:
-------------------------------------------------------
> what's the EXPLAIN output on the queries?

mysql> explain select SQL_CALC_FOUND_ROWS `Content`.`contentId`,`Content`.`MembershipID`,`Content`.`SourceGroup`,`Content`.`TypeID`,`Content`.`CategoryID`,`Content`.`Name`,`Content`.`Location`,`Content`.`Filesize`,`Content`.`OrigCRC`,`Content`.`Description`,`Content`.`ESRB`,`Content`.`Thumbnail`,`Content`.`Private`,`Content`.`Default`,`Content`.`Uploaded`,`Content`.`Progress`,`Content`.`Views`,`Content`.`MetterID`,`Content`.`Active`,`Content`.`TimeUpdated`,`Content`.`TimeCreated`, `Users`.`FirstName` as Author from `Content` INNER JOIN `Memberships` ON ( `Content`.`MembershipID` = `Memberships`.`MembershipID` AND `Memberships`.`SourceID` = '42' ) INNER JOIN `Users` ON ( `Memberships`.`UserID` = `Users`.`UserID` ) where `Content`.`TypeID` = '8' AND`Content`.`Uploaded` = '2' AND `Content`.`Active` = '1' order by `Content`.`TimeCreated` desc LIMIT 0,5;
+----+-------------+-------------+--------+-------------------------------------+---------+---------+-----------------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+-------------------------------------+---------+---------+-----------------------------+------+-----------------------------+
| 1 | SIMPLE | Content | ref | Active,Uploaded,MembershipID,TypeID | Active | 1 | const | 10 | Using where; Using filesort |
| 1 | SIMPLE | Memberships | eq_ref | PRIMARY,SourceID | PRIMARY | 4 | Player.Content.MembershipID | 1 | Using where |
| 1 | SIMPLE | Users | eq_ref | PRIMARY | PRIMARY | 4 | Player.Memberships.UserID | 1 | |
+----+-------------+-------------+--------+-------------------------------------+---------+---------+-----------------------------+------+-----------------------------+
3 rows in set (0.00 sec)


----------------

mysql> explain select `Content`.`MembershipID` from `Content` INNER JOIN `Memberships` ON ( `Content`.`MembershipID` = `Memberships`.`MembershipID` AND `Memberships`.`SourceID` = '42' ) INNER JOIN `Users` ON ( `Memberships`.`UserID` = `Users`.`UserID` ) where `Content`.`TypeID` = '8' AND`Content`.`Uploaded` = '2' AND `Content`.`Active` = '1' order by `Content`.`TimeCreated` desc LIMIT 0,5;
+----+-------------+-------------+--------+-------------------------------------+---------+---------+-----------------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+-------------------------------------+---------+---------+-----------------------------+------+-----------------------------+
| 1 | SIMPLE | Content | ref | Active,Uploaded,MembershipID,TypeID | Active | 1 | const | 10 | Using where; Using filesort |
| 1 | SIMPLE | Memberships | eq_ref | PRIMARY,SourceID | PRIMARY | 4 | Player.Content.MembershipID | 1 | Using where |
| 1 | SIMPLE | Users | eq_ref | PRIMARY | PRIMARY | 4 | Player.Memberships.UserID | 1 | |
+----+-------------+-------------+--------+-------------------------------------+---------+---------+-----------------------------+------+-----------------------------+
3 rows in set (0.00 sec)

--------------

mysql> explain select `Content`.`contentId`,`Content`.`MembershipID`,`Content`.`SourceGroup`, `Content`.`TypeID`,`Content`.`CategoryID`,`Content`.`Name`,`Content`.`Location`, `Content`.`Filesize`,`Content`.`OrigCRC`,`Content`.`Description`,`Content`.`ESRB`, `Content`.`Thumbnail`,`Content`.`Private`,`Content`.`Default`,`Content`.`Uploaded`, `Content`.`Progress`,`Content`.`Views`,`Content`.`MetterID`,`Content`.`Active`, `Content`.`TimeUpdated`,`Content`.`TimeCreated`, `Users`.`FirstName` as Author from `Content` INNER JOIN `Memberships` ON ( `Content`.`MembershipID` = `Memberships`.`MembershipID` AND `Memberships`.`SourceID` = '42' ) INNER JOIN `Users` ON ( `Memberships`.`UserID` = `Users`.`UserID` ) where `Content`.`TypeID` = '8' AND`Content`.`Uploaded` = '2' AND `Content`.`Active` = '1' order by `Content`.`TimeCreated` desc LIMIT 0,5;
+----+-------------+-------------+--------+-------------------------------------+--------------+---------+---------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+-------------------------------------+--------------+---------+---------------------------------+------+---------------------------------+
| 1 | SIMPLE | Memberships | ref | PRIMARY,SourceID | SourceID | 4 | const | 53 | Using temporary; Using filesort |
| 1 | SIMPLE | Content | ref | Active,Uploaded,MembershipID,TypeID | MembershipID | 4 | Player.Memberships.MembershipID | 13 | Using where |
| 1 | SIMPLE | Users | eq_ref | PRIMARY | PRIMARY | 4 | Player.Memberships.UserID | 1 | |
+----+-------------+-------------+--------+-------------------------------------+--------------+---------+---------------------------------+------+---------------------------------+
3 rows in set (0.00 sec)

>
> what version of cluster are you running?
>

5.0.24

> what options (condition pushdown?)?

I didn't have condition pushdown enabled, however I ran

SET engine_condition_pushdown=on;

And I didn't get any performance push. The main difference I can see in the explains is the use of temporary table on my non-cluster sql (third one).

Any ideas how to fix the problem? I do appreciate it!