Kanary Posted June 16, 2013 Share Posted June 16, 2013 Dear experts, I'm experiencing Server 503 errors and latency in loading pages specially in the BO because of my database slow queries. I'm running my website under a powerful VPS6 and My Hosting company have reposted that there are many SQL queries which are consuming the server resources and slowing down the whole website performance. The full slow queries log can be found here: http://kanaryluxuryw...om/slow-new.txt Here is a sample: # Time: 130616 9:34:35 # [email protected]: canariwa_ps1502[canariwa_ps1502] @ localhost  # Query_time: 8.607168 Lock_time: 7.531808 Rows_sent: 1 Rows_examined: 62512 SET timestamp=1371364475; SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value, COUNT(DISTINCT p.id_product) nbr, lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name FROM ps144_feature_product fp INNER JOIN ps144_product p ON (p.id_product = fp.id_product) LEFT JOIN ps144_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 1) INNER JOIN ps144_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0)) LEFT JOIN ps144_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 1) LEFT JOIN ps144_layered_indexable_feature_lang_value lifl ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 1) LEFT JOIN ps144_layered_indexable_feature_value_lang_value lifvl ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 1) INNER JOIN `ps144_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 14) LEFT JOIN `ps144_manufacturer` m ON (m.id_manufacturer = p.id_manufacturer) WHERE p.`active` = 1 AND fp.id_feature = 16 AND p.id_product IN ( SELECT id_product FROM ps144_category_product cp INNER JOIN ps144_category c ON (c.id_category = cp.id_category AND c.nleft >= 54 AND c.nright <= 59 AND c.active = 1)) AND p.id_product IN (SELECT id_product FROM ps144_feature_product fp WHERE fp.`id_feature_value` = 67) AND p.id_product IN (SELECT id_product FROM ps144_feature_product fp WHERE fp.`id_feature_value` = 45) AND p.id_manufacturer IN (11) I need an expert who can help me optimize these queries, please help me know which module is causing this slow queries and how to modify them. Waiting your expert reply, Regards, Kanary. Link to comment Share on other sites More sharing options...
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!Register a new account
Already have an account? Sign in here.Sign In Now