MySQL Forums
Forum List  »  Performance

Slow Mysql optimizer
Posted by: Jeganathan Thangavel
Date: February 03, 2010 04:02AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Mysql optimizer
3964
February 03, 2010 04:02AM
1606
February 04, 2010 09:30AM
1139
February 16, 2010 11:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.