Optimizing the query with respect to doctrine
Code:
SELECT DISTINCT y.company_id AS y__company_id, y.service_price AS y__service_price, y.print_service AS y__print_service, y.company_name_en AS y__company_name_en, y.gps_latitude AS y__gps_latitude, y.gps_longitude AS y__gps_longitude, y.company_name_ar AS y__company_name_ar, y.url AS y__url, (y.service_price4 + IFNULL((SELECT SUM(y2.revenue_actual) AS y2__0 FROM yp_comp_keywords_revenue y2 INNER JOIN yp_keyword_master y3 ON y2.keyword_id = y3.keyword_id WHERE ((y3.keyword_en = CONCAT("trading",'') OR FIND_IN_SET( CONCAT("trading",''),keyword_synonyms_en)) AND y2.company_id = y.company_id AND y2.start_date <= curdate() AND y2.end_date >= curdate()) GROUP BY y2.company_id), 0)) AS y__0, (y.print_service + 0) AS y__1, y.company_name_en AS y__2, y.keywordmaster_en AS y__3, y.company_keywords_en AS y__4, y.keyword_hide_en AS y__5 FROM yp_companies y WHERE 1 AND (MATCH(company_name_en,company_synonyms_en) AGAINST('"trading"' in BOOLEAN MODE) OR FIND_IN_SET( "trading" , company_keywords_en)) HAVING (y__0 > 0 OR y__1> 0) ORDER BY y__0 DESC, y.print_service DESC, y.company_name_en
this is the explain plan of the query
HTML Code:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY y ALL NULL NULL NULL NULL 211009 Using where; Using filesort
2 DEPENDENT SUBQUERY y2 ref company_id company_id 4 uae2009.y.company_id 3 Using where; Using index
2 DEPENDENT SUBQUERY y3 eq_ref PRIMARY,keyword_en,keyword_en_2 PRIMARY 4 uae2009.y2.keyword_id 1 Using where
if i remove the order by clause , the query is little fast
Please help me to solve the problem
thanks in advance
kavitha
