Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  MIN(
    IF(
      prices.percentage_discount = 0, 
      prices.price, 
      prices.price - (
        prices.price * prices.percentage_discount
      )/ 100
    )
  ) as price, 
  products.product_type, 
  products.parent_product_id, 
  products.rf_stop_update_price, 
  products.rf_stop_update_amount, 
  products.rf_stop_update_status 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'en' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_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') 
  AND cscart_categories.storefront_id IN (0, 1) 
WHERE 
  1 
  AND cscart_categories.category_id IN (7313, 7314, 7342) 
  AND companies.status IN ('A') 
  AND products.company_id IN (2, 8, 9, 10, 0) 
  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) 
  AND products.parent_product_id = 0 
  AND products.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  price desc, 
  products.product_id ASC 
LIMIT 
  576, 96

Query time 0.01795

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.47"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 0,
              "filtered": "4.00",
              "cost_info": {
                "read_cost": "1.35",
                "eval_cost": "0.01",
                "prefix_cost": "1.36",
                "data_read_per_join": "320"
              },
              "used_columns": [
                "category_id",
                "usergroup_ids",
                "status",
                "storefront_id"
              ],
              "attached_condition": "((`nuie_scalesta_net`.`cscart_categories`.`category_id` in (7313,7314,7342)) and ((`nuie_scalesta_net`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`nuie_scalesta_net`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`nuie_scalesta_net`.`cscart_categories`.`usergroup_ids`))) and (`nuie_scalesta_net`.`cscart_categories`.`status` in ('A','H')) and (`nuie_scalesta_net`.`cscart_categories`.`storefront_id` in (0,1)))"
            }
          },
          {
            "table": {
              "table_name": "companies",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 0,
              "filtered": "33.33",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "0.25",
                "eval_cost": "0.02",
                "prefix_cost": "1.63",
                "data_read_per_join": "1K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ],
              "attached_condition": "((`nuie_scalesta_net`.`companies`.`status` = 'A') and (`nuie_scalesta_net`.`companies`.`company_id` in (2,8,9,10,0)))"
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "nuie_scalesta_net.cscart_categories.category_id"
              ],
              "rows_examined_per_scan": 117,
              "rows_produced_per_join": 23,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.06",
                "eval_cost": "2.35",
                "prefix_cost": "4.05",
                "data_read_per_join": "376"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "age_verification",
                "status",
                "idx_parent_product_id",
                "my_supplier_id",
                "supplier_access_time",
                "products_rf_product_code_index",
                "products_rf_manufacturer_code_index"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "nuie_scalesta_net.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "5.00",
              "cost_info": {
                "read_cost": "3.53",
                "eval_cost": "0.12",
                "prefix_cost": "9.93",
                "data_read_per_join": "5K"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "usergroup_ids",
                "parent_product_id",
                "rf_stop_update_price",
                "rf_stop_update_amount",
                "rf_stop_update_status"
              ],
              "attached_condition": "((`nuie_scalesta_net`.`products`.`company_id` = `nuie_scalesta_net`.`companies`.`company_id`) and (`nuie_scalesta_net`.`products`.`parent_product_id` = 0) and ((`nuie_scalesta_net`.`products`.`usergroup_ids` = '') or (0 <> find_in_set(0,`nuie_scalesta_net`.`products`.`usergroup_ids`)) or (0 <> find_in_set(1,`nuie_scalesta_net`.`products`.`usergroup_ids`))) and (`nuie_scalesta_net`.`products`.`status` = 'A') and (`nuie_scalesta_net`.`products`.`product_type` <> 'D'))"
            }
          },
          {
            "table": {
              "table_name": "descr1",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "product_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "nuie_scalesta_net.products_categories.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.18",
                "eval_cost": "0.12",
                "prefix_cost": "10.22",
                "data_read_per_join": "4K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product"
              ]
            }
          },
          {
            "table": {
              "table_name": "prices",
              "access_type": "ref",
              "possible_keys": [
                "usergroup",
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "key": "usergroup",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "nuie_scalesta_net.products_categories.product_id"
              ],
              "rows_examined_per_scan": 16,
              "rows_produced_per_join": 1,
              "filtered": "9.92",
              "cost_info": {
                "read_cost": "0.30",
                "eval_cost": "0.19",
                "prefix_cost": "12.47",
                "data_read_per_join": "46"
              },
              "used_columns": [
                "product_id",
                "price",
                "percentage_discount",
                "lower_limit",
                "usergroup_id"
              ],
              "attached_condition": "((`nuie_scalesta_net`.`prices`.`lower_limit` = 1) and (`nuie_scalesta_net`.`prices`.`usergroup_id` in (0,0,1)))"
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_type	rf_stop_update_price	rf_stop_update_amount	rf_stop_update_status
100704	WAS020BN-M	Roxor	P	N	N	N
100705	WAS020CO-M	Roxor	P	N	N	N
100706	WAS020G-M	Roxor	P	N	N	N
100707	WAS020N-M	Roxor	P	N	N	N
100708	WAS030-P	Roxor	P	N	N	N
100709	WAS030BC-M	Roxor	P	N	N	N
100710	WAS030BCO-M	Roxor	P	N	N	N
100711	WAS030BG-M	Roxor	P	N	N	N
100712	WAS030BN-M	Roxor	P	N	N	N
100713	WAS030CO-M	Roxor	P	N	N	N
100714	WAS030G-M	Roxor	P	N	N	N
100715	WAS030N-M	Roxor	P	N	N	N
100716	WAS040-M	Roxor	P	N	N	N
100717	WAS040BC-M	Roxor	P	N	N	N
100718	WAS040BCO-M	Roxor	P	N	N	N
100719	WAS040BG-M	Roxor	P	N	N	N
100720	WAS040BN-M	Roxor	P	N	N	N
100721	WAS040BS-M	Roxor	P	N	N	N
100722	WAS040CO-M	Roxor	P	N	N	N
100723	WAS040G-M	Roxor	P	N	N	N
100724	WAS040N-M	Roxor	P	N	N	N
100725	WAS040WH-M	Roxor	P	N	N	N
100726	WAS050-P	Roxor	P	N	N	N
100727	WAS050BC-M	Roxor	P	N	N	N
100728	WAS050BCO-M	Roxor	P	N	N	N
100729	WAS050BG-M	Roxor	P	N	N	N
100730	WAS050BN-M	Roxor	P	N	N	N
100731	WAS050CO-M	Roxor	P	N	N	N
100732	WAS050G-M	Roxor	P	N	N	N
100733	WAS050N-M	Roxor	P	N	N	N
100734	WAS070-P	Roxor	P	N	N	N
100735	WAS070BC-M	Roxor	P	N	N	N
100736	WAS070BCO-M	Roxor	P	N	N	N
100737	WAS070BG-M	Roxor	P	N	N	N
100738	WAS070BN-M	Roxor	P	N	N	N
100739	WAS070CO-M	Roxor	P	N	N	N
100740	WAS070G-M	Roxor	P	N	N	N
100741	WAS070N-M	Roxor	P	N	N	N
100742	WAS080-M	Roxor	P	N	N	N
100743	WAS080BC-M	Roxor	P	N	N	N
100744	WAS080BCO-M	Roxor	P	N	N	N
100745	WAS080BG-M	Roxor	P	N	N	N
100746	WAS080BN-M	Roxor	P	N	N	N
100747	WAS080CO-M	Roxor	P	N	N	N
100748	WAS080G-M	Roxor	P	N	N	N
100749	WAS080N-M	Roxor	P	N	N	N
100750	WAS107-P	Roxor	P	N	N	N
100751	WAS107B-P	Roxor	P	N	N	N
100752	WAS107BC-M	Roxor	P	N	N	N
100753	WAS107BCO-M	Roxor	P	N	N	N
100754	WAS107BG-M	Roxor	P	N	N	N
100755	WAS107BN-M	Roxor	P	N	N	N
100756	WAS107BS-M	Roxor	P	N	N	N
100757	WAS107CO-M	Roxor	P	N	N	N
100758	WAS107F-P	Roxor	P	N	N	N
100759	WAS107G-M	Roxor	P	N	N	N
100760	WAS107GM-P	Roxor	P	N	N	N
100761	WAS107IG-P	Roxor	P	N	N	N
100762	WAS107KG-P	Roxor	P	N	N	N
100763	WAS107M-P	Roxor	P	N	N	N
100764	WAS107ME-P	Roxor	P	N	N	N
100765	WAS107MW-P	Roxor	P	N	N	N
100766	WAS107N-M	Roxor	P	N	N	N
100767	WAS107PG-P	Roxor	P	N	N	N
100768	WAS107PW-P	Roxor	P	N	N	N
100769	WAS107R-P	Roxor	P	N	N	N
100770	WAS110-P	Roxor	P	N	N	N
100771	WAS110B-P	Roxor	P	N	N	N
100772	WAS110BC-M	Roxor	P	N	N	N
100773	WAS110BCO-M	Roxor	P	N	N	N
100774	WAS110BG-M	Roxor	P	N	N	N
100775	WAS110BN-M	Roxor	P	N	N	N
100776	WAS110BS-M	Roxor	P	N	N	N
100777	WAS110CO-M	Roxor	P	N	N	N
100778	WAS110F-P	Roxor	P	N	N	N
100779	WAS110G-M	Roxor	P	N	N	N
100780	WAS110GM-P	Roxor	P	N	N	N
100781	WAS110IG-P	Roxor	P	N	N	N
100782	WAS110KG-P	Roxor	P	N	N	N
100783	WAS110M-P	Roxor	P	N	N	N
100784	WAS110MW-P	Roxor	P	N	N	N
100785	WAS110N-M	Roxor	P	N	N	N
100786	WAS110PG-P	Roxor	P	N	N	N
100787	WAS110PW-P	Roxor	P	N	N	N
100788	WAS110R-P	Roxor	P	N	N	N
100789	WAS219-M	Roxor	P	N	N	N
100790	WAS219BC-M	Roxor	P	N	N	N
100791	WAS219BCO-M	Roxor	P	N	N	N
100792	WAS219BG-M	Roxor	P	N	N	N
100793	WAS219BN-M	Roxor	P	N	N	N
100794	WAS219CO-M	Roxor	P	N	N	N
100795	WAS219G-M	Roxor	P	N	N	N
100796	WAS219N-M	Roxor	P	N	N	N
100797	WAS220-P	Roxor	P	N	N	N
100798	WAS220B-P	Roxor	P	N	N	N
100799	WAS220BC-M	Roxor	P	N	N	N