Nested SubQuery Performance/Problem
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.