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 = 'ru' 
  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 = 'ru' 
  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 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 (
      18661, 
      18662, 
      18664, 
      19131, 
      19820, 
      19857, 
      19858, 
      19859, 
      19860, 
      19863, 
      19865, 
      19866, 
      19868, 
      19869, 
      19870, 
      19871, 
      19872, 
      19873, 
      19874, 
      19875, 
      19876, 
      19877, 
      19878, 
      19879, 
      19880, 
      19881, 
      19882, 
      19883, 
      19884, 
      19885, 
      19886, 
      19887, 
      19888, 
      19889, 
      19890, 
      19891, 
      19892, 
      19893, 
      19894, 
      19895, 
      19896, 
      19897, 
      19898, 
      19899, 
      19900, 
      19901, 
      19902, 
      19903, 
      19904, 
      19905, 
      19906, 
      19907, 
      19908, 
      19909, 
      19910, 
      19911, 
      19912, 
      19913, 
      19914, 
      19915, 
      19916, 
      19917, 
      19918, 
      19919, 
      19920, 
      19923, 
      19925, 
      20065, 
      20087, 
      20088, 
      20089, 
      20090, 
      20091, 
      20092, 
      20093, 
      20094, 
      20154, 
      20155, 
      20159, 
      20486, 
      20488, 
      20489, 
      20490, 
      20491, 
      20492, 
      20576, 
      20700, 
      20701, 
      20702, 
      20703, 
      20704, 
      20705, 
      20706, 
      20707, 
      20708, 
      20723, 
      20724, 
      20725, 
      20726, 
      20727, 
      20728, 
      20910, 
      20911, 
      20912, 
      20913, 
      20914, 
      20915, 
      20917, 
      20954, 
      20955, 
      20956, 
      20957, 
      20958, 
      20959, 
      20971, 
      20972, 
      20973, 
      20974, 
      21078, 
      21102, 
      21103, 
      21272, 
      21653, 
      21654, 
      21655, 
      21656, 
      21657, 
      21658, 
      21659, 
      21660, 
      21661, 
      21662, 
      21663, 
      21664, 
      21665, 
      21666, 
      21667, 
      21668, 
      21669, 
      21670, 
      21672, 
      21673, 
      21674, 
      21675, 
      21676, 
      21677, 
      21678, 
      21679, 
      21680, 
      21681, 
      21682, 
      21683, 
      21684, 
      21685, 
      21686, 
      21687, 
      21688, 
      21689, 
      21690, 
      21691, 
      21692, 
      21693, 
      21694, 
      21695, 
      21696, 
      21697, 
      21698, 
      21699, 
      21700, 
      21701, 
      21702, 
      21703, 
      21704, 
      21705, 
      21706, 
      21707, 
      21708, 
      21709, 
      21710, 
      21711, 
      21712, 
      21713, 
      21714, 
      21715, 
      21716, 
      21717, 
      21718, 
      21719, 
      21720, 
      21721, 
      21722, 
      21723, 
      21724, 
      21725, 
      21726, 
      21727, 
      21728, 
      21729, 
      21730, 
      21731, 
      21732, 
      21733, 
      21734, 
      21735, 
      21736, 
      21737, 
      21738, 
      21739, 
      21740, 
      21741, 
      21742, 
      21743, 
      21744, 
      21745, 
      21746, 
      21748, 
      21749, 
      21885, 
      21886, 
      21887, 
      21888, 
      21889, 
      21892, 
      21923, 
      21924, 
      21925, 
      21926, 
      21927, 
      22039, 
      22040, 
      22041, 
      22118, 
      22277, 
      22669, 
      22994, 
      22995, 
      22996, 
      23285, 
      23286, 
      23287, 
      23288, 
      23375, 
      23376, 
      23377, 
      23378, 
      23379, 
      23380, 
      23410, 
      23411, 
      23514, 
      19867, 
      19921, 
      19922, 
      20487, 
      20678, 
      20699, 
      20722, 
      21533, 
      21890, 
      21891, 
      21893, 
      21894, 
      22119, 
      22120, 
      22121, 
      22122, 
      22201, 
      22202, 
      22203, 
      22204, 
      22205, 
      22206, 
      22207, 
      22208, 
      22209, 
      22210, 
      22211, 
      22212, 
      22213, 
      22214, 
      22215, 
      22218, 
      22238, 
      22368, 
      22369, 
      22487, 
      22488, 
      22489, 
      23284
    )
  ) 
GROUP BY 
  product_id 
ORDER BY 
  instock_sorting DESC, 
  product asc, 
  products.product_id ASC 
LIMIT 
  96, 24

Query time 0.01449

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
            }
          },
          {
            "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": 289,
                  "filtered": 90.50141144,
                  "index_condition": "products.product_id in (18661,18662,18664,19131,19820,19857,19858,19859,19860,19863,19865,19866,19868,19869,19870,19871,19872,19873,19874,19875,19876,19877,19878,19879,19880,19881,19882,19883,19884,19885,19886,19887,19888,19889,19890,19891,19892,19893,19894,19895,19896,19897,19898,19899,19900,19901,19902,19903,19904,19905,19906,19907,19908,19909,19910,19911,19912,19913,19914,19915,19916,19917,19918,19919,19920,19923,19925,20065,20087,20088,20089,20090,20091,20092,20093,20094,20154,20155,20159,20486,20488,20489,20490,20491,20492,20576,20700,20701,20702,20703,20704,20705,20706,20707,20708,20723,20724,20725,20726,20727,20728,20910,20911,20912,20913,20914,20915,20917,20954,20955,20956,20957,20958,20959,20971,20972,20973,20974,21078,21102,21103,21272,21653,21654,21655,21656,21657,21658,21659,21660,21661,21662,21663,21664,21665,21666,21667,21668,21669,21670,21672,21673,21674,21675,21676,21677,21678,21679,21680,21681,21682,21683,21684,21685,21686,21687,21688,21689,21690,21691,21692,21693,21694,21695,21696,21697,21698,21699,21700,21701,21702,21703,21704,21705,21706,21707,21708,21709,21710,21711,21712,21713,21714,21715,21716,21717,21718,21719,21720,21721,21722,21723,21724,21725,21726,21727,21728,21729,21730,21731,21732,21733,21734,21735,21736,21737,21738,21739,21740,21741,21742,21743,21744,21745,21746,21748,21749,21885,21886,21887,21888,21889,21892,21923,21924,21925,21926,21927,22039,22040,22041,22118,22277,22669,22994,22995,22996,23285,23286,23287,23288,23375,23376,23377,23378,23379,23380,23410,23411,23514,19867,19921,19922,20487,20678,20699,20722,21533,21890,21891,21893,21894,22119,22120,22121,22122,22201,22202,22203,22204,22205,22206,22207,22208,22209,22210,22211,22212,22213,22214,22215,22218,22238,22368,22369,22487,22488,22489,23284)",
                  "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 = 'ru')"
            }
          },
          {
            "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 = 'ru')"
            }
          },
          {
            "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": "products_categories",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "pt"],
              "key": "pt",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["lillyshop_lillyshop.products.product_id"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["category_id"],
              "ref": ["lillyshop_lillyshop.products_categories.category_id"],
              "rows": 1,
              "filtered": 100,
              "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": "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 parent_product_ids product_type parent_product_id instock_marker instock_sorting
21103 База цветная DNKA Cover base №090 Wizard светоотражающая зеленая, 12 мл LILLYSHOP 21103 P 0 P 0 1 1
21923 База цветная DNKA Cover base №093 Fine Work молочная с поталью, 12 мл LILLYSHOP 21923 P 0 P 0 1 1
21924 База цветная DNKA Cover base №094 Prosecco молочная с блестками, 12 мл LILLYSHOP 21924 P 0 P 0 1 1
21925 База цветная DNKA Cover base №095 Coctail молочная с поталью, 12 мл LILLYSHOP 21925 P 0 P 0 1 1
21926 База цветная DNKA Cover base №096 Black Dress черная с поталью, 12 мл LILLYSHOP 21926 P 0 P 0 1 1
21927 База цветная DNKA Cover base №097 Disco Ball светоотражающая с мальчишками, 12 мл LILLYSHOP 21927 P 0 P 0 1 1
19923 Бескислотный праймер (ультрабонд) DNKA Ultrabond, 12 мл LILLYSHOP 19923 P 0 P 0 1 1
22995 Верхние формы DNKA Long Square арочные, 120 шт LILLYSHOP 22995 P 0 P 0 1 1
21272 Верхние формы DNKA балерина 120 шт LILLYSHOP 21272 P 0 P 0 1 1
22669 Верхние формы DNKA квадрат, 120 шт LILLYSHOP 22669 P 0 P 0 1 1
22118 Верхние формы DNKA, миндаль, 120 шт LILLYSHOP 22118 P 0 P 0 1 1
20488 Гель DNKA Builder Gel №01 Vibe прозрачный, 30 мл LILLYSHOP 20488 P 0 P 0 1 1
20489 Гель DNKA Builder Gel №02 Angel белый, 30 мл LILLYSHOP 20489 P 0 P 0 1 1
20490 Гель DNKA Builder Gel №03 Icon розовый, 30 мл LILLYSHOP 20490 P 0 P 0 1 1
20491 Гель DNKA Builder Gel №04 Satin кремовый, 30 мл LILLYSHOP 20491 P 0 P 0 1 1
20492 Гель DNKA Builder Gel №05 Charm nude, 30 мл LILLYSHOP 20492 P 0 P 0 1 1
21888 Гель DNKA Builder Gel №06 Celebrity прозрачная с блестками, 30 мл LILLYSHOP 21888 P 0 P 0 1 1
21887 Гель DNKA Builder Gel №07 Pink Pollen розовый с серебряным шиммером, 30 мл LILLYSHOP 21887 P 0 P 0 1 1
21886 Гель DNKA Builder Gel №08 Peonies лавандовый с серебряным шиммером, 30 мл LILLYSHOP 21886 P 0 P 0 1 1
21885 Гель DNKA Builder Gel №09 Ice kiss молочный опал, 30 мл LILLYSHOP 21885 P 0 P 0 1 1
20911 Гель желе DNKA Jelly Gel №02 Vanila, 15 мл LILLYSHOP 20911 P 0 P 0 1 1
20912 Гель желе DNKA Jelly Gel №03 Fetis, 15 мл LILLYSHOP 20912 P 0 P 0 1 1
20913 Гель желе DNKA Jelly Gel №04 Mania, 15 мл LILLYSHOP 20913 P 0 P 0 1 1
20915 Гель желе DNKA Jelly Gel №06 Idol, 15 мл LILLYSHOP 20915 P 0 P 0 1 1