Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS (
    CASE WHEN products.parent_product_id <> 0 THEN products.parent_product_id ELSE products.product_id END
  ) AS product_id, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.product, descr1.product
  ) as product, 
  companies.company as company_name, 
  GROUP_CONCAT(
    products.product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_ids, 
  GROUP_CONCAT(
    products.product_type 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_types, 
  GROUP_CONCAT(
    products.parent_product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS parent_product_ids, 
  products.product_type, 
  products.parent_product_id, 
  1 as instock_marker, 
  IF(
    products.product_type = 'C', 
    (
      SELECT 
        MAX(amount) 
      FROM 
        cscart_products 
      WHERE 
        parent_product_id = products.product_id
    ) > 0, 
    IF(
      products.amount > 0, 
      1, 
      IF(products.tracking = 'O', 1, 0)
    )
  ) as instock_sorting 
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) 
  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 = 'en' 
  LEFT JOIN cscart_images_links as img_links ON img_links.object_id = products.product_id 
  AND img_links.object_type = 'product' 
  AND img_links.type = 'M' 
WHERE 
  1 
  AND cscart_categories.category_id IN (
    434, 693, 760, 744, 435, 489, 765, 792, 
    1021
  ) 
  AND companies.status IN ('A') 
  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 img_links.pair_id > 0 
  AND (
    1 
    AND products.product_id IN (
      6682, 
      6683, 
      6684, 
      6685, 
      6686, 
      6687, 
      6688, 
      6689, 
      6690, 
      6691, 
      6692, 
      6693, 
      6694, 
      6695, 
      6696, 
      6697, 
      6698, 
      6699, 
      6700, 
      6701, 
      6702, 
      6703, 
      6704, 
      6705, 
      6706, 
      6707, 
      6708, 
      6709, 
      6710, 
      6711, 
      6712, 
      6713, 
      6714, 
      6715, 
      6716, 
      6717, 
      6718, 
      6719, 
      6720, 
      6721, 
      6722, 
      6723, 
      6724, 
      6725, 
      6726, 
      6727, 
      6728, 
      6729, 
      6730, 
      6731, 
      6732, 
      6733, 
      6734, 
      6735, 
      6736, 
      6737, 
      6738, 
      6739, 
      6740, 
      6741, 
      6742, 
      6743, 
      6744, 
      6745, 
      6746, 
      6747, 
      6748, 
      6749, 
      6750, 
      6751, 
      6752, 
      6753, 
      6754, 
      6755, 
      6756, 
      6757, 
      6758, 
      6759, 
      6760, 
      6761, 
      6762, 
      6763, 
      6764, 
      6765, 
      6766, 
      6767, 
      6768, 
      6769, 
      6770, 
      6771, 
      6772, 
      6773, 
      6774, 
      6775, 
      6776, 
      6777, 
      6778, 
      6779, 
      6780, 
      6781, 
      6782, 
      6783, 
      6784, 
      6785, 
      6786, 
      6787, 
      6788, 
      6789, 
      6790, 
      6791, 
      6793, 
      6794, 
      6795, 
      6796, 
      6797, 
      6798, 
      6799, 
      6800, 
      6801, 
      6802, 
      6803, 
      6804, 
      6805, 
      6806, 
      6807, 
      6808, 
      6809, 
      6810, 
      6811, 
      6812, 
      6813, 
      6814, 
      6912, 
      6913, 
      6914, 
      6915, 
      6916, 
      6917, 
      6918, 
      6919, 
      6920, 
      6921, 
      6922, 
      6923, 
      6924, 
      7084, 
      7172, 
      7411, 
      7914, 
      8000, 
      8008, 
      8241, 
      8680, 
      8688, 
      8742, 
      8743, 
      8801, 
      8802, 
      8865, 
      9495, 
      10195, 
      10246, 
      10640, 
      10778, 
      10992, 
      11052, 
      11059, 
      11103, 
      11104, 
      11211, 
      11375, 
      11378, 
      20369, 
      20370, 
      20371, 
      21995, 
      21996, 
      22344, 
      12797, 
      12811, 
      20788, 
      12121
    )
  ) 
GROUP BY 
  product_id 
ORDER BY 
  instock_sorting DESC, 
  product asc, 
  products.product_id ASC 
LIMIT 
  72, 24

Query time 0.00726

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "if(products.product_type = 'C',(subquery#2) > 0,if(products.amount > 0,1,if(products.tracking = 'O',1,0))) desc, if(shared_descr.product_id is not null,shared_descr.product,descr1.product), products.product_id",
      "temporary_table": {
        "filesort": {
          "sort_key": "case when products.parent_product_id <> 0 then products.parent_product_id else products.product_id end",
          "temporary_table": {
            "nested_loop": [
              {
                "table": {
                  "table_name": "companies",
                  "access_type": "system",
                  "possible_keys": ["PRIMARY"],
                  "rows": 1,
                  "filtered": 100
                }
              },
              {
                "table": {
                  "table_name": "cscart_categories",
                  "access_type": "range",
                  "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["category_id"],
                  "rows": 9,
                  "filtered": 100,
                  "index_condition": "cscart_categories.category_id in (434,693,760,744,435,489,765,792,1021)",
                  "attached_condition": "(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)"
                }
              },
              {
                "table": {
                  "table_name": "products_categories",
                  "access_type": "ref",
                  "possible_keys": ["PRIMARY", "pt"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["category_id"],
                  "ref": ["lillyshop_lillyshop.cscart_categories.category_id"],
                  "rows": 26,
                  "filtered": 100,
                  "using_index": true
                }
              },
              {
                "table": {
                  "table_name": "products",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY", "status"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "ref": ["lillyshop_lillyshop.products_categories.product_id"],
                  "rows": 1,
                  "filtered": 90.50141144,
                  "index_condition": "products.product_id in (6682,6683,6684,6685,6686,6687,6688,6689,6690,6691,6692,6693,6694,6695,6696,6697,6698,6699,6700,6701,6702,6703,6704,6705,6706,6707,6708,6709,6710,6711,6712,6713,6714,6715,6716,6717,6718,6719,6720,6721,6722,6723,6724,6725,6726,6727,6728,6729,6730,6731,6732,6733,6734,6735,6736,6737,6738,6739,6740,6741,6742,6743,6744,6745,6746,6747,6748,6749,6750,6751,6752,6753,6754,6755,6756,6757,6758,6759,6760,6761,6762,6763,6764,6765,6766,6767,6768,6769,6770,6771,6772,6773,6774,6775,6776,6777,6778,6779,6780,6781,6782,6783,6784,6785,6786,6787,6788,6789,6790,6791,6793,6794,6795,6796,6797,6798,6799,6800,6801,6802,6803,6804,6805,6806,6807,6808,6809,6810,6811,6812,6813,6814,6912,6913,6914,6915,6916,6917,6918,6919,6920,6921,6922,6923,6924,7084,7172,7411,7914,8000,8008,8241,8680,8688,8742,8743,8801,8802,8865,9495,10195,10246,10640,10778,10992,11052,11059,11103,11104,11211,11375,11378,20369,20370,20371,21995,21996,22344,12797,12811,20788,12121)",
                  "attached_condition": "products.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` = 'A'"
                }
              },
              {
                "table": {
                  "table_name": "shared_descr",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY", "product_id", "company_id"],
                  "key": "PRIMARY",
                  "key_length": "13",
                  "used_key_parts": ["product_id", "lang_code", "company_id"],
                  "ref": [
                    "lillyshop_lillyshop.products_categories.product_id",
                    "const",
                    "const"
                  ],
                  "rows": 1,
                  "filtered": 100,
                  "attached_condition": "trigcond(shared_descr.lang_code = 'en')"
                }
              },
              {
                "table": {
                  "table_name": "descr1",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY", "product_id"],
                  "key": "PRIMARY",
                  "key_length": "9",
                  "used_key_parts": ["product_id", "lang_code"],
                  "ref": [
                    "lillyshop_lillyshop.products_categories.product_id",
                    "const"
                  ],
                  "rows": 1,
                  "filtered": 100,
                  "attached_condition": "trigcond(descr1.lang_code = 'en')"
                }
              },
              {
                "table": {
                  "table_name": "prices",
                  "access_type": "ref",
                  "possible_keys": [
                    "usergroup",
                    "product_id",
                    "lower_limit",
                    "usergroup_id"
                  ],
                  "key": "usergroup",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "ref": ["lillyshop_lillyshop.products_categories.product_id"],
                  "rows": 1,
                  "filtered": 99.99145508,
                  "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)",
                  "using_index": true
                }
              },
              {
                "table": {
                  "table_name": "img_links",
                  "access_type": "ref",
                  "possible_keys": ["PRIMARY", "object_id"],
                  "key": "object_id",
                  "key_length": "81",
                  "used_key_parts": ["object_id", "object_type", "type"],
                  "ref": [
                    "lillyshop_lillyshop.products_categories.product_id",
                    "const",
                    "const"
                  ],
                  "rows": 1,
                  "filtered": 100,
                  "index_condition": "img_links.object_id = products_categories.product_id and img_links.object_type = 'product' and img_links.`type` = 'M'",
                  "attached_condition": "img_links.pair_id > 0"
                }
              }
            ],
            "subqueries": [
              {
                "expression_cache": {
                  "state": "uninitialized",
                  "query_block": {
                    "select_id": 2,
                    "nested_loop": [
                      {
                        "table": {
                          "table_name": "cscart_products",
                          "access_type": "ALL",
                          "possible_keys": ["idx_parent_product_id"],
                          "rows": 11707,
                          "filtered": 100,
                          "attached_condition": "cscart_products.parent_product_id = products.product_id"
                        }
                      }
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
}

Result

product_id	product	company_name	product_ids	product_types	product_type	instock_marker
6748	Gel polish Kira Nails #072 orange enamel, 6 ml	LILLYSHOP	6748	P	P	1
8801	Gel polish Kira Nails #073 sunny yellow enamel, 6 ml	LILLYSHOP	8801	P	P	1
8802	Gel polish Kira Nails #074 pale yellow enamel, 6 ml	LILLYSHOP	8802	P	P	1
11103	Gel polish Kira Nails #075 light yellow enamel, 6 ml	LILLYSHOP	11103	P	P	1
6744	Gel polish Kira Nails #076 menthol enamel, 6 ml	LILLYSHOP	6744	P	P	1
6745	Gel polish Kira Nails #077 light green enamel, 6 ml	LILLYSHOP	6745	P	P	1
6746	Gel polish Kira Nails #078 green enamel, 6 ml	LILLYSHOP	6746	P	P	1
6747	Gel polish Kira Nails #079 rich green enamel, 6 ml	LILLYSHOP	6747	P	P	1
10992	Gel polish Kira Nails #080 dark blue enamel, 6 ml	LILLYSHOP	10992	P	P	1
6749	Gel polish Kira Nails #081 blue enamel, 6 ml	LILLYSHOP	6749	P	P	1
6750	Gel polish Kira Nails #082 blue enamel, 6 ml	LILLYSHOP	6750	P	P	1
6752	Gel polish Kira Nails #085 purple-gray enamel, 6 ml	LILLYSHOP	6752	P	P	1
6753	Gel polish Kira Nails #086 greenish-turquoise enamel, 6 ml	LILLYSHOP	6753	P	P	1
6754	Gel polish Kira Nails #087 light lilac enamel, 6 ml	LILLYSHOP	6754	P	P	1
6755	Gel polish Kira Nails #088 pale lilac enamel, 6 ml	LILLYSHOP	6755	P	P	1
6756	Gel polish Kira Nails #089 lilac-pink enamel, 6 ml	LILLYSHOP	6756	P	P	1
6757	Gel polish Kira Nails #090 muted pink enamel, 6 ml	LILLYSHOP	6757	P	P	1
6758	Gel polish Kira Nails #091 pale purple-pink enamel, 6 ml	LILLYSHOP	6758	P	P	1
6759	Gel polish Kira Nails #092 dark lilac enamel, 6 ml	LILLYSHOP	6759	P	P	1
6760	Gel polish Kira Nails #093 pink enamel, 6 ml	LILLYSHOP	6760	P	P	1
6915	Gel polish Kira Nails #094 muted pink enamel, 6 ml	LILLYSHOP	6915	P	P	1
6916	Gel polish Kira Nails #095 deep red with micro-glitter, 6 ml	LILLYSHOP	6916	P	P	1
6761	Gel polish Kira Nails #096 bright pink neon enamel, 6 ml	LILLYSHOP	6761	P	P	1
6762	Gel polish Kira Nails #097 very bright pink neon enamel, 6 ml	LILLYSHOP	6762	P	P	1