I have some problems with my CS-Cart 4.2. I've got around 700k filters and 180k products. Sometimes, it takes around 170 seconds for query to complete.
d | User | Host | db | Command | Time | State | Info | Progress |
+-------+------------------+-----------+-------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 659 | root | localhost | | Sleep | 207 | | | 0.000 |
| 13462 | kitxenon_autolux | localhost | kitxenon_bk | Query | 2 | Waiting for table level lock | REPLACE INTO cscart_product_features_values (`product_id`, `lang_code`, `feature_id`, `variant_id`) | 0.000 |
| 13466 | kitxenon_autolux | localhost | kitxenon_bk | Sleep | 2 | | | 0.000 |
| 13950 | kitxenon_autolux | localhost | kitxenon_bk | Query | 1 | Waiting for table level lock | SELECT v.feature_id, v.value, v.value_int, v.variant_id, f.feature_type, fd.description, fd.prefix, | 0.000 |
| 14077 | kitxenon_autolux | localhost | kitxenon_bk | Query | 2 | Waiting for table level lock | SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.displa | 0.000 |
| 14095 | kitxenon_autolux | localhost | kitxenon_bk | Query | 109 | Sending data | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14107 | kitxenon_autolux | localhost | kitxenon_bk | Query | 103 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14116 | kitxenon_autolux | localhost | kitxenon_bk | Query | 96 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14139 | kitxenon_autolux | localhost | kitxenon_bk | Query | 76 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14142 | kitxenon_autolux | localhost | kitxenon_bk | Query | 2 | Waiting for table level lock | SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.displa | 0.000 |
| 14149 | kitxenon_autolux | localhost | kitxenon_bk | Query | 69 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14154 | kitxenon_autolux | localhost | kitxenon_bk | Query | 66 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14169 | kitxenon_autolux | localhost | kitxenon_bk | Query | 52 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14175 | kitxenon_autolux | localhost | kitxenon_bk | Query | 49 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14178 | kitxenon_autolux | localhost | kitxenon_bk | Query | 47 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14186 | kitxenon_autolux | localhost | kitxenon_bk | Query | 2 | Waiting for table level lock | SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.displa | 0.000 |
| 14196 | kitxenon_autolux | localhost | kitxenon_bk | Query | 33 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14212 | kitxenon_autolux | localhost | kitxenon_bk | Sleep | 1 | | | 0.000 |
| 14217 | kitxenon_autolux | localhost | kitxenon_bk | Query | 2 | Waiting for table level lock | SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.displa | 0.000 |
| 14222 | kitxenon_autolux | localhost | kitxenon_bk | Query | 11 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount | 0.000 |
| 14229 | kitxenon_autolux | localhost | kitxenon_bk | Query | 2 | Waiting for table level lock | SELECT cscart_product_features_values.feature_id, COUNT(DISTINCT cscart_products.product_id) as prod | 0.000 |
| 14240 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+-------+------------------+-----------+-------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+
