Slow Mysql optimizer
The sub queries are spending most of the time in preparing stage.
We have the following query pattern with sub queries and like search.
SELECT DISTINCT(a.post_id), a.post_date as post_dt, a.cat_id, FIND_IN_SET( '1', a.acl_ids ) AS priority FROM table1 AS a, table2 AS b WHERE a.post_id = b.post_id AND a.post_id IN ( SELECT post_id FROM table1 WHERE ( (CONCAT('#', LOWER(post_values), '#') LIKE '%#text#%' OR post_values = 'any') OR (CONCAT('#', LOWER(post_values), '#') LIKE '%#text#%' OR post_values = 'any')) AND (field_id = 412 OR field_id = 413 ) AND cat_id = 41 AND post_id IN ( SELECT post_id FROM table1 WHERE ( ( post_values BETWEEN 1 AND 1 AND field_id = 414 AND cat_id = 41 ) OR ( post_values BETWEEN 1 AND 1 AND field_id = 415 AND cat_id = 41 ) OR ( post_values <= 1 AND field_id = 414 AND cat_id = 41 AND post_id IN( SELECT post_id FROM table1 WHERE post_values >= 1 AND field_id = 415 AND cat_id = 41 ))) AND post_id IN ( SELECT post_id FROM table1 WHERE ( CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR CONCAT('#', post_values, '#') LIKE '%#some text#%' OR (CONCAT('#', post_values, '#') LIKE '%#some text#%' OR post_values = 'any') AND field_id= 416 AND cat_id = 41 ) AND post_id IN ( SELECT post_id FROM table1 WHERE ( (CONCAT('#', LOWER(post_values), '#') LIKE '%#some text#%' OR post_values = 'any') OR (CONCAT('#', LOWER(post_values), '#') LIKE '%#some text#%' OR post_values = 'any')) AND field_id = 455 AND cat_id = 41 AND post_id IN ( SELECT post_id FROM table1 WHERE ( post_values = 'some text') AND field_id = 418 AND cat_id = 41 ))))) AND status=1 AND a.cat_id IN (41) AND b.loc_id = 1 ORDER BY priority DESC, post_date DESC
For these queries the mysql optimizer is taking time and all such queries are spending 5 to 10 seconds in preparing stage. Can somebody help us in optimizing this query. Is it possible to convert this query into joins?
We are using mysql 5.1.43 64 bit in Fedora Linux.
Thanks!