Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Sql Optimisation Rate Topic   - - - - -

 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 18 April 2014 - 07:14 AM #1

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 ?

# 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;


 
  • cscartrocks
  • Member
  • Members
  • Join Date: 24-Jan 11
  • 1837 posts

Posted 18 April 2014 - 07:16 AM #2

Do you have any custom addon/mod?

One Step Checkout Addon - The ultimate checkout experience
Best CS-Cart SEO addon - CS-Cart SEO Ultimate Addon
PM for 365-day support and custom development service


 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 18 April 2014 - 07:31 AM #3

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

 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 12038 posts

Posted 18 April 2014 - 08:55 PM #4

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.

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 21 April 2014 - 06:24 AM #5

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.

 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 12038 posts

Posted 22 April 2014 - 12:06 AM #6

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.

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 

Posted 22 April 2014 - 02:07 AM #7

Upgrade MySQL...
I've moved on from CS-Cart to WooC******** - If you need anything I can be of little help.

 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 23 April 2014 - 12:07 PM #8

Upgrade MySQL...

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

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.


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

 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 12038 posts

Posted 23 April 2014 - 08:36 PM #9

"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.

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 

Posted 23 April 2014 - 09:35 PM #10

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.


More fuel = bigger engine.

You are trying to run jet engine in a lawnmower.

Lastest Sql engine is 5.5
I've moved on from CS-Cart to WooC******** - If you need anything I can be of little help.

 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 24 April 2014 - 04:22 AM #11

"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.



 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 24 April 2014 - 04:27 AM #12

"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.


Attached is Print Screen of CSC Database Page...

Attached Thumbnails

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


 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 24 April 2014 - 04:47 AM #13

More fuel = bigger engine.

You are trying to run jet engine in a lawnmower.

Lastest Sql engine is 5.5


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

Attached Thumbnails

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


 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 18 August 2014 - 06:48 AM #14

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 :

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.


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...

 
  • cscartrocks
  • Member
  • Members
  • Join Date: 24-Jan 11
  • 1837 posts

Posted 18 August 2014 - 07:33 AM #15

Have you tried another hosting?

One Step Checkout Addon - The ultimate checkout experience
Best CS-Cart SEO addon - CS-Cart SEO Ultimate Addon
PM for 365-day support and custom development service


 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 18 August 2014 - 07:48 AM #16

Have you tried another hosting?

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.

 

Posted 18 August 2014 - 10:49 AM #17

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.
I've moved on from CS-Cart to WooC******** - If you need anything I can be of little help.

 
  • Alecomp
  • Senior Member
  • Trial users
  • Join Date: 13-Jul 12
  • 342 posts

Posted 18 August 2014 - 05:31 PM #18

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.

 
  • ymul
  • Member
  • Trial users
  • Join Date: 09-Aug 13
  • 41 posts

Posted 18 August 2014 - 07:42 PM #19

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.

Mysql version on new host is 5.5.27

 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 21727 posts

Posted 18 August 2014 - 07:48 PM #20

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.


Did you face this issue in 4.x version? As far as I remember, it was fixed in 3.0.6
GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 345     Multi-Vendor              USD 1250    Multi-Vendor PLUS           USD 3100 (2775)
CS-Cart Ultimate  USD 775     CS-Cart + YOUPI      USD 545      Multi-Vendor Ultimate       USD 7500 (6000)