Slow query in cluster but not outside
Posted by:
Alex Llera
Date: September 27, 2006 03:11PM
Does anyone have any idea what is causing this? Both of these queries run on a SQL node of the cluster against 4 DB nodes (2 replicas).
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;
5 rows in set (2.84 sec)
-----------------
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;
5 rows in set (0.98 sec)
-----------------------------
Now I run this on my non-cluster setup:
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;
5 rows in set (0.01 sec)
Those are huge differences between the three, and the first and third return more columns than the middle one. What would cause this behavior? Is there a way to correct it? I don't know if I can use Mysql Cluster if my queries are going to differ that much in speed.
I do apologize for the lack of table aliasing in this example.