Sql Optimisation

My provider told me that some SQL queries where too long/slow and are consuming too much ressources on the MySQL server.



I found the queries in the logs. here is one line but the logs contains hundreds of similar lines…



Can somebody tell me where they are coming from and what I could do ?


```php

Query_time: 12 Lock_time: 0 Rows_sent: 3 Rows_examined: 1999152

SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price, descr1.short_description, IF(descr1.short_description = '', descr1.full_description, '') as full_description, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product, IF(shared_descr.product_id IS NOT NULL, shared_descr.short_description, descr1.short_description) as short_description, IF(shared_descr.product_id IS NOT NULL, IF(shared_descr.short_description = '', shared_descr.full_description, ''), IF(descr1.short_description = '', descr1.full_description, '')) as full_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_keywords, descr1.meta_keywords) as meta_keywords, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_description, descr1.meta_description) as meta_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.search_words, descr1.search_words) as search_words, IF(shared_descr.product_id IS NOT NULL, shared_descr.promo_text, descr1.promo_text) as promo_text, IF(shared_prices.product_id IS NOT NULL,MIN(IF(shared_prices.percentage_discount = 0, shared_prices.price, shared_prices.price - (shared_prices.price * shared_prices.percentage_discount)/100)),MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100))) as price, cscart_seo_names.name as seo_name FROM cscart_products as products LEFT JOIN cscart_product_features_values ON cscart_product_features_values.product_id = products.product_id AND cscart_product_features_values.lang_code = 'fr' LEFT JOIN (SELECT product_id, GROUP_CONCAT(cscart_product_features_values.variant_id) AS advanced_variants FROM cscart_product_features_values WHERE lang_code = 'fr' GROUP BY product_id) AS pfv_advanced ON pfv_advanced.product_id = products.product_id LEFT JOIN cscart_product_features_values as var_val_11_9 ON var_val_11_9.product_id = products.product_id AND var_val_11_9.lang_code = 'fr' LEFT JOIN cscart_product_features_values as var_val_11_10 ON var_val_11_10.product_id = products.product_id AND var_val_11_10.lang_code = 'fr' LEFT JOIN cscart_product_features_values as var_val_9_7 ON var_val_9_7.product_id = products.product_id AND var_val_9_7.lang_code = 'fr' LEFT JOIN cscart_product_features_values as var_val_9_8 ON var_val_9_8.product_id = products.product_id AND var_val_9_8.lang_code = 'fr' LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'fr' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id AND shared_descr.company_id = 1 AND shared_descr.lang_code = 'fr' LEFT JOIN cscart_ult_product_prices as shared_prices ON shared_prices.product_id = products.product_id AND shared_prices.lower_limit = 1 AND shared_prices.usergroup_id IN (0, 0, 1) AND shared_prices.company_id = 1 LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'fr' AND ( cscart_seo_names.company_id = 1 OR cscart_seo_names.company_id = 0) WHERE 1 AND ( FIND_IN_SET('8', advanced_variants)) AND ( FIND_IN_SET('35', advanced_variants)) AND ((var_val_11_9.value_int >= 1 AND var_val_11_9.value_int <= 1 AND var_val_11_9.value = '' AND var_val_11_9.feature_id = 35) OR (var_val_11_10.value_int >= 2 AND var_val_11_10.value_int <= 2 AND var_val_11_10.value = '' AND var_val_11_10.feature_id = 35)) AND ((var_val_9_7.value_int >= 6 AND var_val_9_7.value_int <= 10 AND var_val_9_7.value = '' AND var_val_9_7.feature_id = 36) OR (var_val_9_8.value_int >= 10 AND var_val_9_8.value_int <= 999 AND var_val_9_8.value = '' AND var_val_9_8.feature_id = 36)) AND cscart_categories.category_id IN (1) AND cscart_categories.company_id = 1 AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id ORDER BY price desc LIMIT 0, 12;

```

Do you have any custom addon/mod?

Hello. Only one : Wg Ship2Pay (Shows payment methods depending on the chosen shipping method)

Looks like a query for a listing page (or filter) of products that shows lots of product information. Suggest you use a different list option that shows less info. However, having the query report nearly 2M rows examined indicates to me that you either have several language defined and/or have a very large number of products. Not knowing what you are displaying nor what your site consists of makes it difficult to identify whether this is a bug or simply that you have your site configured to provide a lot of detail on generally high level queries.



I'm also guessing you are running under a shared hosting plan (otherwise your host wouldn't complain). I would recommend you move to a VPS if you are going to run a real ecommerce business. Share hosting should be used for information sites only, not database intensive sites like a shopping cart.

It could be a query for filtering products. But my site contains only 40 products, with 38 features, in 3 languages. I think that is not a huge amount of information.



The same site and amount of information was running since more than 5 years (under Magento, Prestashop, Virtuemart and finally CSC since 2 years) without any complain.



I'm running a shared hosting plan. I think I will move to a VPS.



Thank you for your answers.

Well, I don't know where you'd get “rows examined: 1999152” for that kind of configuration.



I would suggest you spend the $7 for the support credits and get cs-cart to look at it for you. And if it turns out it's a bug, then it will be free and others will benefit from the change in a future release.

Upgrade MySQL…

[quote name='JesseLeeStringer' timestamp='1398132464' post='182036']

Upgrade MySQL…

[/quote]

I think my provider will not do that upgrade just for me… And I don't understand how this should help to reduce the size of that enormous queries.



MySQL Version : 5.0

DB size : 11,818,692 octets

[quote name='tbirnseth' timestamp='1398125178' post='182027']

Well, I don't know where you'd get “rows examined: 1999152” for that kind of configuration.



I would suggest you spend the $7 for the support credits and get cs-cart to look at it for you. And if it turns out it's a bug, then it will be free and others will benefit from the change in a future release.

[/quote]



I'll follow your suggestion and ask for support from CSC. Thanks.

“octets” with 8's and 9's in them? An octet is a base-8 number and hence can only contain digits from 0-7.

[quote name='ymul' timestamp='1398254839' post='182182']

I think my provider will not do that upgrade just for me… And I don't understand how this should help to reduce the size of that enormous queries.

[/quote]



More fuel = bigger engine.



You are trying to run jet engine in a lawnmower.



Lastest Sql engine is 5.5

[quote name='tbirnseth' timestamp='1398285373' post='182220']

“octets” with 8's and 9's in them? An octet is a base-8 number and hence can only contain digits from 0-7.

[/quote]

[quote name='tbirnseth' timestamp='1398285373' post='182220']

“octets” with 8's and 9's in them? An octet is a base-8 number and hence can only contain digits from 0-7.

[/quote]



Attached is Print Screen of CSC Database Page…

Capture d’écran 2014-04-24 à 06.21.30.png

[quote name='JesseLeeStringer' timestamp='1398288926' post='182223']

More fuel = bigger engine.



You are trying to run jet engine in a lawnmower.



Lastest Sql engine is 5.5

[/quote]



Now Distrib 5.5.35, no amelioration. Not sure my site/provider is a lawnmower…

Capture d’écran 2014-04-24 à 06.43.13.png

After more complains from my provider, I had to choose between installing another solution for my shop (!) or move to a VPS. My site is now on a VPS and the cas is now closed for my provider. But the problem is not resolved…



As suggested above, I opened a ticket, here is the conclusion of it :

[quote]Thank you for your message. I am afraid that we can not attempt to optimize these queries within our technical support service, so we can offer you the customization services of our affiliate company Simtech Development Ltd (it is our ex-Custom Development department now acting as a separate legal entity). Their website is www.simtechdev.com. For your convenience we can forward your request to Simtech Development Ltd for estimation, please let me know if you are interested in it.Thank you.

[/quote]



I'm suprised that nobody at CSC can find out where these queries are coming from… I'm also suprised to see that a helpdesk ticket is not the way to obtain a real technical support (what's the use of my support credits ?) and that I have to use the service of an affiliate company.



I'll will now do the upgrade from 4.1.5 to 4.2.1, maybe it will help. But if I encounter as many problems as with previous upgrades, I'll really think about alternatives…

Have you tried another hosting?

[quote name='cscartrocks' timestamp='1408347182' post='189801']

Have you tried another hosting?

[/quote]

I've changed to a VPS server. Global performances are now better.

But in my sense it has nothing to do with the host. These queries are on > 1'000'000 rows and returning 3 rows. This should not be normal on such a simple configuration. I'm almost sure that it has something to do with the product filters.

Product Filters = Database Engine.

Older versions of mySQL didn't support speed improvements made by later releases.



You are correct, product filters - but also look at your database/php version on your new host.

what is the size of this table cscart_product_features_values ? i've had a similar problem. the reason was in multiple time doubled products features. so when any filter was applied it walked through all these features.

[quote name='JesseLeeStringer' timestamp='1408358964' post='189817']

Product Filters = Database Engine.

Older versions of mySQL didn't support speed improvements made by later releases.

You are correct, product filters - but also look at your database/php version on your new host.

[/quote]

Mysql version on new host is 5.5.27

[quote name='Alecomp' timestamp='1408383062' post='189875']

what is the size of this table cscart_product_features_values ? i've had a similar problem. the reason was in multiple time doubled products features. so when any filter was applied it walked through all these features.

[/quote]



Did you face this issue in 4.x version? As far as I remember, it was fixed in 3.0.6