MySQL Forums
Forum List  »  Performance

Nested SubQuery Performance/Problem
Posted by: Frank Osterberg
Date: November 15, 2005 11:19AM

Hi,

I have a problem with this particular subquery in that it apparently hangs indefinitely (with status “sending data”) or it is just extremely slow (so that i never see it finish). Maybe it is a badly written query, but when i execute each sub query separately and then use the result of that sub query for the outer query then it works very fast, just as a combined query with subqueries it seems to be slow.

What i want is to get all the newest (highest auto increment id) entry of the table test for each Line that exists. Each Line is referenced by multiple Jobs and each Job is referenced multiple Tests.

Now I spend some time to create an sql script to create a test database, tables & values to reproduce this problem, so if you are interested and want to see and example or just more information about the structure then you can use that.

Anyhow, here is the troublesome query:
SELECT *
FROM Test
WHERE TestID IN (
SELECT MAX(TestID)
FROM Test
WHERE _JobID IN (
SELECT MAX(JobID)
FROM Job
GROUP BY _LineID
)
GROUP BY _JobID
)
now the above query hangs for me apperently indefinitly

Now if i execute the most inner subquery:
SELECT MAX(JobID)
FROM Job
GROUP BY _LineID
i get: 7362, 7370, 7364, 7372, 7345, 7368, 7360, 7369, 7367, 7373 in 0.0054 sec

if then execute the second most inner subquery with the result of first query:
SELECT MAX(TestID)
FROM Test
WHERE _JobID IN (
7362, 7370, 7364, 7372, 7345, 7368, 7360, 7369, 7367, 7373
)
GROUP BY _JobID
i get: 7358272, 7368067, 7372932, 7359654, 7368233, 7367324, 7371906, 7370896, 7371683, 7371106 in 0,018 sec

if then execute the outer most query with the above result as follows:
SELECT *
FROM Test
WHERE TestID IN (
7358272, 7368067, 7372932, 7359654, 7368233, 7367324, 7371906, 7370896, 7371683, 7371106
)
i get the 10 results i'm looking for in less then 0.0015 sec, that to my knowledge means that the problem is not the query but something with MySql and subqueries.

Now here is the script to create the database, tables and insert the values:
(but please note that: it creates about 7 million records and therefore may take a bit)

CREATE DATABASE `SubQueryTest`;
Use `SubQueryTest`;
DROP TABLE IF EXISTS `test`;
DROP TABLE IF EXISTS `job`;
DROP TABLE IF EXISTS `line`;
CREATE TABLE `line` (
`LineID` INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
`Name` CHAR(50) collate latin1_general_ci NOT NULL default ''
) TYPE=InnoDB;
CREATE TABLE `job` (
`JobID` INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
`_LineID` INT UNSIGNED default NULL,
`StartDateTime` DATETIME NOT NULL,
CONSTRAINT F_Job_Line FOREIGN KEY (`_LineID`) REFERENCES line(`LineID`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `test` (
`TestID` INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
`_JobID` INT UNSIGNED NOT NULL,
`TestValue` DOUBLE NOT NULL,
CONSTRAINT F_Test_Job FOREIGN KEY (`_JobID`) REFERENCES job(`JobID`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
set @var := 0;
insert into line (lineid, name) select @var := (@var + 1) as id, concat("Line ",@var) as name from mysql.help_keyword where @var < 10;
set @var := 0;
insert into job (jobid, _lineid, startdatetime) select @var := (@var + 1) as id, (floor(rand()*10)+1) as lineid, from_unixtime(unix_timestamp(now())*rand()) as sometime from mysql.help_keyword h1, mysql.help_keyword h2 where @var < 7373;
set @var := 0;
insert into test (testid, _jobid, testvalue) select @var := (@var + 1) as id, (floor(rand()*7373)+1) as jobid, rand() as sometvalue from mysql.help_keyword h1, mysql.help_keyword h2, mysql.help_keyword h3 where @var < 7373737;

Help/Info/Reesponse is much appreciated! :)

Thanks!!



Edited 2 time(s). Last edit at 11/16/2005 04:27AM by Frank Osterberg.

Options: ReplyQuote


Subject
Views
Written By
Posted
Nested SubQuery Performance/Problem
7989
November 15, 2005 11:19AM
2744
November 16, 2005 01:56PM
3277
November 17, 2005 08:53AM
2081
November 18, 2005 05:02AM
2284
December 30, 2005 02:44AM
2171
December 31, 2005 04:10PM
1696
November 16, 2005 06:44PM


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.