sgugliaarrows.it
Date: Thu Jan 17 2008 - 04:00:49 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]