I have a query that takes a long time to return, roughly 12-15 seconds, which I'd like to optimize. The query below is the result of running the original query with SHOW WARNINGS. If I run EXPLAIN EXTENDED on this query (and it's predecessor) it shows me that a PRIMARY select_type is derived, has null possible keys, null key, null key_len, and 314883 rows (see below).
Here's my query:
SELECT DISTINCT `steps`.`TestStepName` AS `TestStepName`
,`test`.`measurements`.`MeasurementName` AS `MeasurementName`
FROM `test`.`measurements`
JOIN (
SELECT `test`.`teststeps`.`TestSteps_ID` AS `TestSteps_ID`
,`test`.`teststeps`.`TestStepName` AS `TestStepName`
FROM `test`.`teststeps`
JOIN (
SELECT `dutdataassembly`.`AssyInTest` AS `AssyInTest`
,`test`.`testinformation`.`TestInformation_ID` AS `TestInformation_ID`
FROM `test`.`testinformation`
JOIN (
SELECT `test`.`dutdata`.`AssyInTest` AS `AssyInTest`
,`test`.`events`.`Event_ID` AS `Event_ID`
FROM `test`.`dutdata`
JOIN `test`.`events`
WHERE (
(`test`.`events`.`EventType_ID` = 7)
AND (`test`.`events`.`Event_ID` = `test`.`dutdata`.`Event_ID`)
AND (
`test`.`dutdata`.`AssyInTest` = '41528F'
)
)
) `dutdataassembly`
WHERE (`test`.`testinformation`.`Event_ID` = `dutdataassembly`.`Event_ID`)
) `testinfo`
WHERE (
(`test`.`teststeps`.`TestInformation_TestInformation_ID` = `testinfo`.`TestInformation_ID`)
AND (`test`.`teststeps`.`NumberOfMeasurements` > 0)
)
) `steps`
WHERE (
(`test`.`measurements`.`TestSteps_TestSteps_ID` = `steps`.`TestSteps_ID`)
AND (`test`.`measurements`.`MeasurementName` <> '')
)
ORDER BY `steps`.`TestStepName`
,`test`.`measurements`.`MeasurementName`
EXPLAIN statement:
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, <derived2>, ALL, , , , , 314883, 100.00, Using temporary; Using filesort
1, PRIMARY, measurements, ref, fk_Measurements_TestSteps1_idx,idx_Measurements,idx_measurementName,idx_justNames, fk_Measurements_TestSteps1_idx, 4, steps.TestSteps_ID, 1, 100.00, Using where
2, DERIVED, <derived3>, ALL, , , , , 16237, 100.00,
2, DERIVED, teststeps, ref, fk_TestSteps_TestInformation1_idx,idx_testinformationid, fk_TestSteps_TestInformation1_idx, 4, testinfo.TestInformation_ID, 9, 100.00, Using where
3, DERIVED, <derived4>, ALL, , , , , 16237, 100.00,
3, DERIVED, testinformation, ref, fk_TestInformation_Events1_idx, fk_TestInformation_Events1_idx, 4, dutdataassembly.Event_ID, 1, 100.00, Using index
4, DERIVED, dutdata, ref, fk_dutdata_Events1_idx,dutdata_index, dutdata_index, 138, , 33116, 100.00, Using where
4, DERIVED, events, eq_ref, PRIMARY,fk_Events_EventType1_idx, PRIMARY, 4, test.dutdata.Event_ID, 1, 100.00, Using where
The table measurements has hundreds of thousands of entries in it and has been indexed. The table teststeps has an order of magnitude fewer entries and has also been indexed.
The desired result of this query is a one to many table where each teststep can have many measurements associated with it.
I hope that's enough information to go on.
Aucun commentaire:
Enregistrer un commentaire