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 (
      7338, 
      19956, 
      19957, 
      19958, 
      19959, 
      19960, 
      19965, 
      19967, 
      19968, 
      19969, 
      19970, 
      19972, 
      19973, 
      19974, 
      19975, 
      19976, 
      20002, 
      20004, 
      20005, 
      20006, 
      20008, 
      20009, 
      20033, 
      20042, 
      20067, 
      20174, 
      20175, 
      20376, 
      20398, 
      20399, 
      20400, 
      20401, 
      20402, 
      20403, 
      20404, 
      20405, 
      20617, 
      20618, 
      20619, 
      21057, 
      21058, 
      21059, 
      21060, 
      21061, 
      21062, 
      21063, 
      21064, 
      21140, 
      21141, 
      21142, 
      21143, 
      21144, 
      21145, 
      21146, 
      21147, 
      21148, 
      21149, 
      21150, 
      21151, 
      21152, 
      21153, 
      21154, 
      21155, 
      21156, 
      21157, 
      21158, 
      21159, 
      21160, 
      21161, 
      21162, 
      21163, 
      21164, 
      21165, 
      21166, 
      21167, 
      21168, 
      21169, 
      21170, 
      21171, 
      21172, 
      21173, 
      21174, 
      21175, 
      21176, 
      21177, 
      21178, 
      21179, 
      21180, 
      21181, 
      21182, 
      21183, 
      21184, 
      21185, 
      21186, 
      21187, 
      21188, 
      21189, 
      21190, 
      21191, 
      21192, 
      21193, 
      21194, 
      21195, 
      21196, 
      21197, 
      21198, 
      21199, 
      21200, 
      21201, 
      21202, 
      21203, 
      21204, 
      21205, 
      21206, 
      21207, 
      21208, 
      21209, 
      21210, 
      21211, 
      21212, 
      21213, 
      21214, 
      21215, 
      21216, 
      21217, 
      21218, 
      21219, 
      21229, 
      21230, 
      21231, 
      21232, 
      21233, 
      21234, 
      21323, 
      21324, 
      21325, 
      21326, 
      21327, 
      21328, 
      21329, 
      21330, 
      21331, 
      21332, 
      21333, 
      21334, 
      21335, 
      21535, 
      21536, 
      21579, 
      21580, 
      21581, 
      21582, 
      21593, 
      21594, 
      21595, 
      21596, 
      21597, 
      21598, 
      21599, 
      21600, 
      21601, 
      21602, 
      21603, 
      21605, 
      21606, 
      21607, 
      21608, 
      21609, 
      21610, 
      21611, 
      21612, 
      21613, 
      21614, 
      21615, 
      21616, 
      21617, 
      21618, 
      21619, 
      21620, 
      21621, 
      21622, 
      21961, 
      21974, 
      21975, 
      21976, 
      21977, 
      21978, 
      21979, 
      21981, 
      21982, 
      21983, 
      21984, 
      22102, 
      22103, 
      22104, 
      22105, 
      22106, 
      22107, 
      22108, 
      22109, 
      22341, 
      22345, 
      22346, 
      22347, 
      22348, 
      22349, 
      22351, 
      22352, 
      22353, 
      22508, 
      22509, 
      22700, 
      22701, 
      22702, 
      22703, 
      22705, 
      22706, 
      23132, 
      23515, 
      23610, 
      23611, 
      23612, 
      23828, 
      23829, 
      23830, 
      23831, 
      23832, 
      23833, 
      23834, 
      23835, 
      23836, 
      23837, 
      23838, 
      23839, 
      23840, 
      23841, 
      23842, 
      23843, 
      23844, 
      23845, 
      23846, 
      23847, 
      23848, 
      23849, 
      23850, 
      23851, 
      23852, 
      23853, 
      23854, 
      23855, 
      8298, 
      9833, 
      9972, 
      12129, 
      19961, 
      19962, 
      19963, 
      19964, 
      19966, 
      19971, 
      20010, 
      20043, 
      20106, 
      20163, 
      20374, 
      20375, 
      21053, 
      21054, 
      21055, 
      21134, 
      21135, 
      21843, 
      21958, 
      21959, 
      21960, 
      21962, 
      21964, 
      21965, 
      21966, 
      21967, 
      21968, 
      21969, 
      21970, 
      21971, 
      21972, 
      21973, 
      21980, 
      22223, 
      22268, 
      22269, 
      22270, 
      22271, 
      22350, 
      22354, 
      22355, 
      22356, 
      22467, 
      22510, 
      22511, 
      22513, 
      22553, 
      22678, 
      22679, 
      22698, 
      22699, 
      22704, 
      22841, 
      23133, 
      23188, 
      23516, 
      23827, 
      23856, 
      23857, 
      23858
    )
  ) 
GROUP BY 
  product_id 
ORDER BY 
  instock_sorting DESC, 
  product asc, 
  products.product_id ASC 
LIMIT 
  24, 24

Query time 0.02615

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": 314,
                  "filtered": 90.50141144,
                  "index_condition": "products.product_id in (7338,19956,19957,19958,19959,19960,19965,19967,19968,19969,19970,19972,19973,19974,19975,19976,20002,20004,20005,20006,20008,20009,20033,20042,20067,20174,20175,20376,20398,20399,20400,20401,20402,20403,20404,20405,20617,20618,20619,21057,21058,21059,21060,21061,21062,21063,21064,21140,21141,21142,21143,21144,21145,21146,21147,21148,21149,21150,21151,21152,21153,21154,21155,21156,21157,21158,21159,21160,21161,21162,21163,21164,21165,21166,21167,21168,21169,21170,21171,21172,21173,21174,21175,21176,21177,21178,21179,21180,21181,21182,21183,21184,21185,21186,21187,21188,21189,21190,21191,21192,21193,21194,21195,21196,21197,21198,21199,21200,21201,21202,21203,21204,21205,21206,21207,21208,21209,21210,21211,21212,21213,21214,21215,21216,21217,21218,21219,21229,21230,21231,21232,21233,21234,21323,21324,21325,21326,21327,21328,21329,21330,21331,21332,21333,21334,21335,21535,21536,21579,21580,21581,21582,21593,21594,21595,21596,21597,21598,21599,21600,21601,21602,21603,21605,21606,21607,21608,21609,21610,21611,21612,21613,21614,21615,21616,21617,21618,21619,21620,21621,21622,21961,21974,21975,21976,21977,21978,21979,21981,21982,21983,21984,22102,22103,22104,22105,22106,22107,22108,22109,22341,22345,22346,22347,22348,22349,22351,22352,22353,22508,22509,22700,22701,22702,22703,22705,22706,23132,23515,23610,23611,23612,23828,23829,23830,23831,23832,23833,23834,23835,23836,23837,23838,23839,23840,23841,23842,23843,23844,23845,23846,23847,23848,23849,23850,23851,23852,23853,23854,23855,8298,9833,9972,12129,19961,19962,19963,19964,19966,19971,20010,20043,20106,20163,20374,20375,21053,21054,21055,21134,21135,21843,21958,21959,21960,21962,21964,21965,21966,21967,21968,21969,21970,21971,21972,21973,21980,22223,22268,22269,22270,22271,22350,22354,22355,22356,22467,22510,22511,22513,22553,22678,22679,22698,22699,22704,22841,23133,23188,23516,23827,23856,23857,23858)",
                  "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
19974 База камуфлирующая Edlen Nude base №08, 17 мл LILLYSHOP 19974 P 0 P 0 1 1
21961 База камуфлирующая Edlen Nude base №08, 30 мл LILLYSHOP 21961 P 0 P 0 1 1
19973 База камуфлирующая Edlen Nude base №09, 17 мл LILLYSHOP 19973 P 0 P 0 1 1
19972 База камуфлирующая Edlen Opal base №10, 17 мл LILLYSHOP 19972 P 0 P 0 1 1
19970 База камуфлирующая Edlen Opal base №11, 17 мл LILLYSHOP 19970 P 0 P 0 1 1
19968 База камуфлирующая Edlen Opal base №12, 17 мл LILLYSHOP 19968 P 0 P 0 1 1
19967 База цветная Edlen Colored base №13, 17 мл LILLYSHOP 19967 P 0 P 0 1 1
21981 База цветная Edlen Colored base №13, 30 мл LILLYSHOP 21981 P 0 P 0 1 1
19965 База цветная Edlen Colored base №14, 17 мл LILLYSHOP 19965 P 0 P 0 1 1
21982 База цветная Edlen Colored base №14, 30 мл LILLYSHOP 21982 P 0 P 0 1 1
20033 База цветная Edlen Colored base №15, 17 мл LILLYSHOP 20033 P 0 P 0 1 1
21983 База цветная Edlen Colored base №15, 30 мл LILLYSHOP 21983 P 0 P 0 1 1
21057 База цветная Edlen Colored base №16, 17 мл LILLYSHOP 21057 P 0 P 0 1 1
21984 База цветная Edlen Colored base №16, 30 мл LILLYSHOP 21984 P 0 P 0 1 1
20398 Бескислотный праймер (ультрабонд) Ultrabond Edlen, 9 мл LILLYSHOP 20398 P 0 P 0 1 1
22341 Гель Edlen Builder Gel Lollipop №12, пудро-розовый, 30 мл LILLYSHOP 22341 P 0 P 0 1 1
22345 Гель Edlen Builder Gel Lollipop №13, розовый, 30 мл LILLYSHOP 22345 P 0 P 0 1 1
22346 Гель Edlen Builder Gel Lollipop №14, желтый, 30 мл LILLYSHOP 22346 P 0 P 0 1 1
22347 Гель Edlen Builder Gel Lollipop №15, пурпурный, 30 мл LILLYSHOP 22347 P 0 P 0 1 1
22348 Гель Edlen Builder Gel Lollipop №16, молочный, 30 мл LILLYSHOP 22348 P 0 P 0 1 1
22349 Гель Edlen Builder Gel Lollipop №17, голубой, 30 мл LILLYSHOP 22349 P 0 P 0 1 1
20404 Гель Edlen Builder Gel №01, 15 мл LILLYSHOP 20404 P 0 P 0 1 1
21593 Гель Edlen Builder Gel №01, 30 мл LILLYSHOP 21593 P 0 P 0 1 1
20402 Гель Edlen Builder Gel №02, 15 мл LILLYSHOP 20402 P 0 P 0 1 1