[EMAIL PROTECTED] schrieb:
Hello everybody!
I have a huge query (something similar to a search engine), full of left joins
and my testing server takes several minutes each time to output the recordset.
I was looking for some (right) way to optimize the search.
there were some solutions proposed around, like split in n simpler queries and
then merge the results, or create some temporary tables containing the filtered
data to let the main query execute without too many iterations...
I was wondering if there was anybody here, to suggest the best approach to this
kind of performance issues.
Thanks,
Stefano.
p.s.: this is a sample of the query we are talking about..
each sub_table has 2 fields: cs_AN (indexed) and a data field (usually text,
not indexed)
SELECT field_1 FROM table_A
LEFT JOIN sub_table_AU ON table_A.mt_AN = sub_table_AU.cs_AN
LEFT JOIN sub_table_BN ON table_A.mt_AN = sub_table_BN.cs_AN
LEFT JOIN sub_table_CN ON table_A.mt_AN = sub_table_CN.cs_AN
LEFT JOIN sub_table_CS ON table_A.mt_AN = sub_table_CS.cs_AN
LEFT JOIN sub_table_DT ON table_A.mt_AN = sub_table_DT.cs_AN
LEFT JOIN sub_table_EM ON table_A.mt_AN = sub_table_EM.cs_AN
LEFT JOIN sub_table_GN ON table_A.mt_AN = sub_table_GN.cs_AN
LEFT JOIN sub_table_ID ON table_A.mt_AN = sub_table_ID.cs_AN
LEFT JOIN sub_table_LA ON table_A.mt_AN = sub_table_LA.cs_AN
LEFT JOIN sub_table_OI ON table_A.mt_AN = sub_table_OI.cs_AN
LEFT JOIN sub_table_OD ON table_A.mt_AN = sub_table_OD.cs_AN
LEFT JOIN sub_table_RN ON table_A.mt_AN = sub_table_RN.cs_AN
LEFT JOIN sub_table_KW ON table_A.mt_AN = sub_table_KW.cs_AN
WHERE ([...]
create a merge table over your sub_tables, search this merge and join
table_A to this, and not otherwise
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
