Buenas,
Alguna idea sobre como optimizar esta consulta en mysql para consultas con unas tablas grandes?? Gracias de antemano a todos!!
Código SQL:
Ver originalSELECT DISTINCT
str.STR_LEVEL - 1 AS level,
str2.STR_LEVEL - 1 AS level2,
str3.STR_LEVEL - 1 AS level3,
str4.STR_LEVEL - 1 AS level4,
ELT(str.STR_LEVEL-1, dest.TEX_TEXT, dest2.TEX_TEXT, dest3.TEX_TEXT, dest4.TEX_TEXT) AS name1,
ELT(str.STR_LEVEL-1, str.STR_ID, str2.STR_ID, str3.STR_ID, str4.STR_ID) AS id1,
ELT(str.STR_LEVEL-2, dest.TEX_TEXT, dest2.TEX_TEXT, dest3.TEX_TEXT, dest4.TEX_TEXT) AS name2,
ELT(str.STR_LEVEL-2, str.STR_ID, str2.STR_ID, str3.STR_ID, str4.STR_ID) AS id2,
ELT(str.STR_LEVEL-3, dest.TEX_TEXT, dest2.TEX_TEXT, dest3.TEX_TEXT, dest4.TEX_TEXT) AS name3,
ELT(str.STR_LEVEL-3, str.STR_ID, str2.STR_ID, str3.STR_ID, str4.STR_ID) AS id3,
ELT(str.STR_LEVEL-4, dest.TEX_TEXT, dest2.TEX_TEXT, dest3.TEX_TEXT, dest4.TEX_TEXT) AS name4,
ELT(str.STR_LEVEL-4, str.STR_ID, str2.STR_ID, str3.STR_ID, str4.STR_ID) AS id4,
ELT(str.STR_LEVEL-1, str.STR_ID_PARENT, str2.STR_ID_PARENT, str3.STR_ID_PARENT, str4.STR_ID_PARENT) AS parent
FROM `tof_SEARCH_TREE` str
LEFT JOIN `tof_SEARCH_TREE` `str2` ON (str2.STR_ID = str.STR_ID_PARENT)
LEFT JOIN `tof_SEARCH_TREE` `str3` ON (str3.STR_ID = str2.STR_ID_PARENT)
LEFT JOIN `tof_SEARCH_TREE` `str4` ON (str4.STR_ID = str3.STR_ID_PARENT)
LEFT JOIN `tof_DESIGNATIONS` `des` ON (des.DES_ID = str.STR_DES_ID AND des.DES_LNG_ID = 8)
LEFT JOIN `tof_DESIGNATIONS` `des2` ON (des2.DES_ID = str2.STR_DES_ID AND des2.DES_LNG_ID = 8)
LEFT JOIN `tof_DESIGNATIONS` `des3` ON (des3.DES_ID = str3.STR_DES_ID AND des3.DES_LNG_ID = 8)
LEFT JOIN `tof_DESIGNATIONS` `des4` ON (des4.DES_ID = str4.STR_DES_ID AND des4.DES_LNG_ID = 8)
LEFT JOIN `tof_DES_TEXTS` `dest` ON (dest.TEX_ID = des.DES_TEX_ID)
LEFT JOIN `tof_DES_TEXTS` `dest2` ON (dest2.TEX_ID = des2.DES_TEX_ID)
LEFT JOIN `tof_DES_TEXTS` `dest3` ON (dest3.TEX_ID = des3.DES_TEX_ID)
LEFT JOIN `tof_DES_TEXTS` `dest4` ON (dest4.TEX_ID = des4.DES_TEX_ID)
INNER JOIN `tof_LINK_GA_STR` lgs ON (lgs.LGS_STR_ID = str.STR_ID)
INNER JOIN `tof_LINK_LA_TYP` lat ON (lat.LAT_TYP_ID = 19224 AND lat.LAT_GA_ID = lgs.LGS_GA_ID)
HAVING (parent = 10001)
ORDER BY name1, name2, name3, name4