MySQL Forums
Forum List  »  NDB clusters

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query in cluster but not outside
2608
September 27, 2006 03:11PM
1186
September 28, 2006 06:56AM
1188
September 28, 2006 03:42PM
1165
September 28, 2006 05:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.