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 (435) 
  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 
  168, 24

Query time 0.00782

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": {
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "system",
              "possible_keys": ["PRIMARY"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "const",
              "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["category_id"],
              "ref": ["const"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "read_sorted_file": {
              "filesort": {
                "sort_key": "case when products.parent_product_id <> 0 then products.parent_product_id else products.product_id end",
                "table": {
                  "table_name": "products",
                  "access_type": "range",
                  "possible_keys": ["PRIMARY", "status"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "rows": 182,
                  "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": "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.product_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(descr1.lang_code = 'en')"
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "pt"],
              "key": "PRIMARY",
              "key_length": "6",
              "used_key_parts": ["category_id", "product_id"],
              "ref": ["const", "lillyshop_lillyshop.products.product_id"],
              "rows": 1,
              "filtered": 100,
              "using_index": true
            }
          },
          {
            "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.product_id", "const", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(shared_descr.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.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.product_id", "const", "const"],
              "rows": 1,
              "filtered": 100,
              "index_condition": "img_links.object_id = products.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
6785	Gel polish Kira Nails No. 123 light green enamel neon, 6 ml	LILLYSHOP	6785	P	P	1
11375	Gel polish Kira Nails No. 124 light green-neon enamel, 6 ml	LILLYSHOP	11375	P	P	1
9495	Gel polish Kira Nails No. 133 gray-blue enamel, 6 ml	LILLYSHOP	9495	P	P	1
6791	Gel polish Kira Nails No. 136 ultramarine enamel, 6 ml	LILLYSHOP	6791	P	P	1
6793	Gel polish Kira Nails No. 137 jade enamel, 6 ml	LILLYSHOP	6793	P	P	1
6799	Gel polish Kira Nails No. 145 orange-red enamel, 6 ml	LILLYSHOP	6799	P	P	1
6914	Gel polish Kira Nails No. 168 sea foam enamel, 6 ml	LILLYSHOP	6914	P	P	1
6733	Kira Nails gel polish #061 dark raspberry enamel, 6 ml	LILLYSHOP	6733	P	P	1
6713	Kira Nails gel polish #065 pink-lilac enamel, 6 ml	LILLYSHOP	6713	P	P	1
8743	Kira Nails gel polish #083 pale cornflower enamel, 6 ml	LILLYSHOP	8743	P	P	1