query duurt te lang
ik heb een query, maar die laadt veel te lang en ik zie geen fouten. Kunnen jullie mij hiermee helpen?
Quote:
SELECT
tabletreeproductgroup.STR_LEVEL,
case
when tabletreeproductgroup.STR_LEVEL=1 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup2.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup3.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup4.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup5.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=1 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup2.STR_ID
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup3.STR_ID
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup4.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup5.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup2.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup3.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup4.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup2.STR_ID
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup3.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup4.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup2.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup3.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup2.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup3.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup2.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup2.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup.STR_ID else null
end
FROM
`tabletreeproductgroup`
INNER JOIN tablelinkgroupscarsandparts ON (tabletreeproductgroup.STR_ID=tablelinkgroupscarsandparts.LGS_STR_ID)
INNER JOIN linkedtablecars ON (linkedtablecars.LAT_GA_ID=tablelinkgroupscarsandparts.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup2 ON (tabletreeproductgroup2.STR_ID = tabletreeproductgroup.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts2 ON (tabletreeproductgroup2.STR_ID=tablelinkgroupscarsandparts2.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars2 ON (linkedtablecars2.LAT_GA_ID=tablelinkgroupscarsandparts2.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup3 ON (tabletreeproductgroup3.STR_ID = tabletreeproductgroup2.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts3 ON (tabletreeproductgroup3.STR_ID=tablelinkgroupscarsandparts3.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars3 ON (linkedtablecars3.LAT_GA_ID=tablelinkgroupscarsandparts3.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup4 ON (tabletreeproductgroup4.STR_ID = tabletreeproductgroup3.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts4 ON (tabletreeproductgroup4.STR_ID=tablelinkgroupscarsandparts4.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars4 ON (linkedtablecars4.LAT_GA_ID=tablelinkgroupscarsandparts4.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup5 ON (tabletreeproductgroup5.STR_ID = tabletreeproductgroup4.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts5 ON (tabletreeproductgroup5.STR_ID=tablelinkgroupscarsandparts5.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars5 ON (linkedtablecars5.LAT_GA_ID=tablelinkgroupscarsandparts5.LGS_GA_ID)
/* -------------------------------- */
WHERE
linkedtablecars.LAT_TYP_ID='$selectedtype' AND
linkedtablecars2.LAT_TYP_ID='$selectedtype' AND
linkedtablecars3.LAT_TYP_ID='$selectedtype' AND
linkedtablecars4.LAT_TYP_ID='$selectedtype' AND
linkedtablecars5.LAT_TYP_ID='$selectedtype'
GROUP BY
tabletreeproductgroup.STR_LEVEL,
tabletreeproductgroup.STR_TEXT,
tabletreeproductgroup.STR_ID,
tabletreeproductgroup2.STR_TEXT,
tabletreeproductgroup2.STR_ID,
tabletreeproductgroup3.STR_TEXT,
tabletreeproductgroup3.STR_ID,
tabletreeproductgroup4.STR_TEXT,
tabletreeproductgroup4.STR_ID,
tabletreeproductgroup5.STR_TEXT,
tabletreeproductgroup5.STR_ID
ORDER BY
tabletreeproductgroup.STR_TEXT,
tabletreeproductgroup2.STR_TEXT,
tabletreeproductgroup3.STR_TEXT,
tabletreeproductgroup4.STR_TEXT,
tabletreeproductgroup5.STR_TEXT
tabletreeproductgroup.STR_LEVEL,
case
when tabletreeproductgroup.STR_LEVEL=1 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup2.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup3.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup4.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup5.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=1 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup2.STR_ID
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup3.STR_ID
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup4.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup5.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup2.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup3.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup4.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=2 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup2.STR_ID
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup3.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup4.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup2.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup3.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=3 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup2.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup3.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup.STR_TEXT
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup2.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=4 then tabletreeproductgroup.STR_ID
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup2.STR_ID else null
end,
/* -------------------------------- */
case
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup.STR_TEXT else null
end,
case
when tabletreeproductgroup.STR_LEVEL=5 then tabletreeproductgroup.STR_ID else null
end
FROM
`tabletreeproductgroup`
INNER JOIN tablelinkgroupscarsandparts ON (tabletreeproductgroup.STR_ID=tablelinkgroupscarsandparts.LGS_STR_ID)
INNER JOIN linkedtablecars ON (linkedtablecars.LAT_GA_ID=tablelinkgroupscarsandparts.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup2 ON (tabletreeproductgroup2.STR_ID = tabletreeproductgroup.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts2 ON (tabletreeproductgroup2.STR_ID=tablelinkgroupscarsandparts2.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars2 ON (linkedtablecars2.LAT_GA_ID=tablelinkgroupscarsandparts2.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup3 ON (tabletreeproductgroup3.STR_ID = tabletreeproductgroup2.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts3 ON (tabletreeproductgroup3.STR_ID=tablelinkgroupscarsandparts3.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars3 ON (linkedtablecars3.LAT_GA_ID=tablelinkgroupscarsandparts3.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup4 ON (tabletreeproductgroup4.STR_ID = tabletreeproductgroup3.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts4 ON (tabletreeproductgroup4.STR_ID=tablelinkgroupscarsandparts4.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars4 ON (linkedtablecars4.LAT_GA_ID=tablelinkgroupscarsandparts4.LGS_GA_ID)
/* -------------------------------- */
LEFT JOIN tabletreeproductgroup AS tabletreeproductgroup5 ON (tabletreeproductgroup5.STR_ID = tabletreeproductgroup4.STR_ID_PARENT)
INNER JOIN tablelinkgroupscarsandparts AS tablelinkgroupscarsandparts5 ON (tabletreeproductgroup5.STR_ID=tablelinkgroupscarsandparts5.LGS_STR_ID)
INNER JOIN linkedtablecars AS linkedtablecars5 ON (linkedtablecars5.LAT_GA_ID=tablelinkgroupscarsandparts5.LGS_GA_ID)
/* -------------------------------- */
WHERE
linkedtablecars.LAT_TYP_ID='$selectedtype' AND
linkedtablecars2.LAT_TYP_ID='$selectedtype' AND
linkedtablecars3.LAT_TYP_ID='$selectedtype' AND
linkedtablecars4.LAT_TYP_ID='$selectedtype' AND
linkedtablecars5.LAT_TYP_ID='$selectedtype'
GROUP BY
tabletreeproductgroup.STR_LEVEL,
tabletreeproductgroup.STR_TEXT,
tabletreeproductgroup.STR_ID,
tabletreeproductgroup2.STR_TEXT,
tabletreeproductgroup2.STR_ID,
tabletreeproductgroup3.STR_TEXT,
tabletreeproductgroup3.STR_ID,
tabletreeproductgroup4.STR_TEXT,
tabletreeproductgroup4.STR_ID,
tabletreeproductgroup5.STR_TEXT,
tabletreeproductgroup5.STR_ID
ORDER BY
tabletreeproductgroup.STR_TEXT,
tabletreeproductgroup2.STR_TEXT,
tabletreeproductgroup3.STR_TEXT,
tabletreeproductgroup4.STR_TEXT,
tabletreeproductgroup5.STR_TEXT
Je hebt (even snel geteld) ca 16 joins, dit betekent dat mysql gaat werken het totaal aantal kolommen van de tabellen die in die joins voorkomen (ook al is het telkens dezelfde tabel).
Waarom heb je die auto gegevens nodig als je een tree aan het opbouwen bent?