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 ( sub_table_AU.cs_AU like '%key1wor%' or sub_table_BN.cs_PU like '%key1wor%' or sub_table_CN.cs_CN like '%key1wor%' or sub_table_CS.cs_CS like '%key1wor%' or sub_table_DT.cs_DT like '%key1wor%' or sub_table_EM.cs_EM like '%key1wor%' or sub_table_GN.cs_GN like '%key1wor%' or sub_table_ID.cs_ID like '%key1wor%' or sub_table_LA.cs_LA like '%key1wor%' or sub_table_OI.cs_OI like '%key1wor%' or sub_table_OD.cs_OD like '%key1wor%' or sub_table_RN.cs_PU like '%key1wor%' or sub_table_KW.cs_KW like '%key1wor%' ) or ( sub_table_AU.cs_AU like '%key2wor%' or sub_table_BN.cs_PU like '%key2wor%' or sub_table_CN.cs_CN like '%key2wor%' or sub_table_CS.cs_CS like '%key2wor%' or sub_table_DT.cs_DT like '%key2wor%' or sub_table_EM.cs_EM like '%key2wor%' or sub_table_GN.cs_GN like '%key2wor%' or sub_table_ID.cs_ID like '%key2wor%' or sub_table_LA.cs_LA like '%key2wor%' or sub_table_OI.cs_OI like '%key2wor%' or sub_table_OD.cs_OD like '%key2wor%' or sub_table_RN.cs_PU like '%key2wor%' or sub_table_KW.cs_KW like '%key2wor%' ... [other OR blocks] ... ) GROUP BY field_1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
