Speed Up Query

The following filter query takes 50 seconds on a dedicated server with tons of ram and cpu.

I've made mysqld adjustments so that temp tables are in memory (not to disk) and it still takes 50 seconds.

The filter is looking for products that match 2 features:

122 - 36 associated variants

93 - 1776 associated variants

I have verified that all the JOIN references are indexed, etc.

This is a version 4.7 cs-cart but I don't see any difference running it in a 4.9.3 environment.

Any thoughts on how to get this to perform?

SELECT cscart_product_filters.filter_id, cscart_product_features_values.variant_id 
FROM cscart_product_features_values 
LEFT JOIN cscart_products as products ON products.product_id = cscart_product_features_values.product_id 
LEFT JOIN cscart_product_filters ON cscart_product_filters.feature_id = cscart_product_features_values.feature_id
LEFT JOIN cscart_product_features ON cscart_product_features.feature_id = cscart_product_features_values.feature_id 
LEFT JOIN cscart_product_options_inventory as inventory ON inventory.product_id = products.product_id 
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_supplier_links ON cscart_supplier_links.object_id = products.product_id AND cscart_supplier_links.object_type = 'P' 
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
LEFT JOIN cscart_product_sales as product_sales ON product_sales.product_id = products.product_id 
LEFT JOIN cscart_product_features_values as var_val_122 ON var_val_122.product_id = products.product_id AND var_val_122.lang_code = 'en' AND var_val_122.feature_id = 122 
LEFT JOIN cscart_product_features_values as var_val_93 ON var_val_93.product_id = products.product_id AND var_val_93.lang_code = 'en' AND var_val_93.feature_id = 93 
WHERE cscart_product_filters.filter_id IN (36, 37, 32) AND cscart_product_features_values.feature_id IN (122, 93, 102) AND cscart_product_features_values.lang_code = 'en' 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 ((var_val_122.variant_id IN (12995) AND var_val_93.variant_id IN (12844) AND cscart_product_features_values.feature_id NOT IN (122, 93)) OR (var_val_93.variant_id IN (12844) AND cscart_product_features_values.feature_id = 122) OR (var_val_122.variant_id IN (12995) AND cscart_product_features_values.feature_id = 93)) AND cscart_product_features.feature_type IN ('S', 'M', 'E') GROUP BY cscart_product_features_values.variant_id

The following filter query takes 50 seconds on a dedicated server with tons of ram and cpu.

I've made mysqld adjustments so that temp tables are in memory (not to disk) and it still takes 50 seconds.

The filter is looking for products that match 2 features:

122 - 36 associated variants

93 - 1776 associated variants

I have verified that all the JOIN references are indexed, etc.

This is a version 4.7 cs-cart but I don't see any difference running it in a 4.9.3 environment.

Any thoughts on how to get this to perform?

SELECT cscart_product_filters.filter_id, cscart_product_features_values.variant_id 
FROM cscart_product_features_values 
LEFT JOIN cscart_products as products ON products.product_id = cscart_product_features_values.product_id 
LEFT JOIN cscart_product_filters ON cscart_product_filters.feature_id = cscart_product_features_values.feature_id
LEFT JOIN cscart_product_features ON cscart_product_features.feature_id = cscart_product_features_values.feature_id 
LEFT JOIN cscart_product_options_inventory as inventory ON inventory.product_id = products.product_id 
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_supplier_links ON cscart_supplier_links.object_id = products.product_id AND cscart_supplier_links.object_type = 'P' 
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
LEFT JOIN cscart_product_sales as product_sales ON product_sales.product_id = products.product_id 
LEFT JOIN cscart_product_features_values as var_val_122 ON var_val_122.product_id = products.product_id AND var_val_122.lang_code = 'en' AND var_val_122.feature_id = 122 
LEFT JOIN cscart_product_features_values as var_val_93 ON var_val_93.product_id = products.product_id AND var_val_93.lang_code = 'en' AND var_val_93.feature_id = 93 
WHERE cscart_product_filters.filter_id IN (36, 37, 32) AND cscart_product_features_values.feature_id IN (122, 93, 102) AND cscart_product_features_values.lang_code = 'en' 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 ((var_val_122.variant_id IN (12995) AND var_val_93.variant_id IN (12844) AND cscart_product_features_values.feature_id NOT IN (122, 93)) OR (var_val_93.variant_id IN (12844) AND cscart_product_features_values.feature_id = 122) OR (var_val_122.variant_id IN (12995) AND cscart_product_features_values.feature_id = 93)) AND cscart_product_features.feature_type IN ('S', 'M', 'E') GROUP BY cscart_product_features_values.variant_id

Hello,

Main problem I have with this is that MySQL / MariaDB is not made for searching. Instead, it is made to work very well with relations. So if you really would want to improve your searching performance, you should look into ElasticSearch / Solr / Sphinx, and not spend an endless amount of time trying to optimize your database queries.

Kind regards,

This is the query used for a standard cs-cart query for retrieving products from a 2 feature filter.

I don't really want to reinvent the world, I'm just trying to find a way to have this return the results in a reasonable time.

I don't know where it's spending its time. It returns 36 rows and 3 columns. Seems like it would be in the query side versus the sort side of things. My guess is that it's the large number of joins. Expecially the ones named var_val_93 (but I'm guessing cuz I'm not a DBA).

A 'Profile' of the query shows that the bulk of time is copying to a tmp table

			25
		
			Copying To Tmp Table
		
			47.7 s

Not sure how big I can make tmp_table_size. Currently it's set to 1024M (1GB) and yet it is still spending time copying to tmp table to disk. Stats show that there have been 1.3M tmp tables created on disk. So it seems to me that the tmp_table_size buffer is getting filled but I'm not sure with what. Hard to imagine that it's eating up 1GB of memory and overflowing versus being flushed. Could it be that some tmp tables that are created are not being DROPPED?

This is the query used for a standard cs-cart query for retrieving products from a 2 feature filter.

I don't really want to reinvent the world, I'm just trying to find a way to have this return the results in a reasonable time.

I don't know where it's spending its time. It returns 36 rows and 3 columns. Seems like it would be in the query side versus the sort side of things. My guess is that it's the large number of joins. Expecially the ones named var_val_93 (but I'm guessing cuz I'm not a DBA).

A 'Profile' of the query shows that the bulk of time is copying to a tmp table

			25
		
			Copying To Tmp Table
		
			47.7 s

Not sure how big I can make tmp_table_size. Currently it's set to 1024M (1GB) and yet it is still spending time copying to tmp table to disk. Stats show that there have been 1.3M tmp tables created on disk. So it seems to me that the tmp_table_size buffer is getting filled but I'm not sure with what. Hard to imagine that it's eating up 1GB of memory and overflowing versus being flushed. Could it be that some tmp tables that are created are not being DROPPED?

Could be the case. I'd say you could decrease page size and increase the InnoDB buffer pool size.

Not quite sure which value(s) you are referring to.

Not sure how a copy to a temp file could take 48 seonds..... Assume the EXPLAIN is not reporting correctly.

Current innodb buffer variables are:

[attachment=14087:twoway_variables.png]

twoway_variables.png

Not sure the EXPLAIN makes much sense Note likes 24 & 25 and the associated summary info.... So what's taking the 48 second?

[attachment=14086:twoway_explain.png]

twoway_explain.png

Not quite sure which value(s) you are referring to.

Not sure how a copy to a temp file could take 48 seonds..... Assume the EXPLAIN is not reporting correctly.

Current innodb buffer variables are:

attachicon.gif twoway_variables.png

Probably what happens is that the heap gets filled by too much as the query could return a load of data. You should set the tmp_table_size and max_heap_table_size parameters to a higher degree to mitigate this.

I had set tmp_table_size to 2GB (from 16MB) and max_heap_table_size to 512MB from the default of 128MB with zero change in performance and it still is writing data to disk temp files. Very confusing.... I'd expect all temp tables to fit into a 2GB buffer.

Hello, I know is not related to this thread, but since I'm new I can't create a post.

I just "Inherited" a website built on CSCart. Performance was really slow (dev environment takes 20 seconds to open the homepage).

This has been work as usual in the development team since early times. I felt it was too slow to work so look into the logs.

I found 10000 queries to the DB just to load the homepage.

Then my question is, is that normal in CS Cart? Is there an incremental approach to fix it? I rather prefer not to rebuild the whole thing.

Well, there could be 10000 reasons why... Cs-cart caches various things in its registry. I.e. Settings, templates associated with a particular controller/mode as well as css and compiled templates themselves.

The most common source of slowness is failure to prune/truncate the cscart_logs table on a monthly or more frequent basis. It can get pretty large and since it's updated by all sorts of areas, there can be quite a wait while the table is locked for update or read. Alternately if your server ownerships/permissions are preventing the var/cache directory tree from being created, then you'll be regenerating everything from scratch on every page load.

You could also have 3rd party JS integrations that are blocking due to a variety of reasons.

Performance analysis can be very time consuming. The first thing I would do is create a local_conf.php file something similar to:

if( $_SERVER['REMOTE_ADDR'] == [your ip address] ) {
  if( !defined('DEVELOPMENT') ) define('DEVELOPMENT', true);
  if( !defined('DEBUG']) define('DEBUG', true);
  ini_set('display_errors', true);
  error_reporting(E_ALL);
}

When you load a page you'll get a bug icon in the upper right of the screen. Click it to open the debugger. There's a lot there and too much to detail here, but that should get you started. The SQL diags are actually pretty good. But don't try to do import/exports with DEBUG enabled. You'll run out of memory.

Is the backend slow as well? Or just the frontend?