[tbirnseth]

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

[poppedweb]

 

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,

[tbirnseth]

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?

[poppedweb]

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.

[tbirnseth]

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:

[tbirnseth]

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

 

[poppedweb]

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:

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.

[tbirnseth]

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.