So many tables join.OH. Can you paste your explain results here?
On 1/17/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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] > > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
