Expert Needed: Slow MySQL queries are killing the website. Database Optimization Required.

[Kanary]

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
# User@Host: 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.

[tomerg3]

Looks like its a Layered Navigation query.

 

You may want to try and get the latest version of the module.

[Kanary]

Thank you tomerg for your answer.

 

Your conclusion is based on the same query or also the other slow query log provided in the link?

 

And how can we make sure, if the newer version of the layered navigation has solved these slow queries problems?

 

Regards,

[tomerg3]

It's based on the query you pasted, I see it references "ps144_layered_price_index"

i do not use layered navigation, so I cannot say if there a new version or if this query was optimized, however, it should cost anything to try

[Cari]

Hi Kanary

How did you get the list of database queries? I want to optimise my shop as well and want to have a look a my queries.

Many thanks!!