SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.product, descr1.product
  ) as product, 
  companies.company as company_name, 
  variation_group_products.group_id AS variation_group_id, 
  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 = 'uk' 
  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 = 'uk' 
  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' 
  INNER JOIN cscart_product_variation_group_products AS variation_group_products ON variation_group_products.product_id = products.product_id 
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 variation_group_products.group_id IN (856) 
GROUP BY 
  products.product_id 
ORDER BY 
  instock_sorting DESC, 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 100

Query time 0.00467

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": "variation_group_products",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "idx_group_id"],
              "key": "idx_group_id",
              "key_length": "3",
              "used_key_parts": ["group_id"],
              "ref": ["const"],
              "rows": 99,
              "filtered": 100,
              "attached_condition": "variation_group_products.group_id <=> 856"
            }
          },
          {
            "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.variation_group_products.product_id"],
              "rows": 1,
              "filtered": 90.50141144,
              "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.variation_group_products.product_id",
                "const",
                "const"
              ],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(shared_descr.lang_code = 'uk')"
            }
          },
          {
            "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.variation_group_products.product_id",
                "const"
              ],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(descr1.lang_code = 'uk')"
            }
          },
          {
            "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.variation_group_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.variation_group_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.variation_group_products.product_id",
                "const",
                "const"
              ],
              "rows": 1,
              "filtered": 100,
              "index_condition": "img_links.object_id = variation_group_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 variation_group_id product_type parent_product_id instock_marker instock_sorting
21626 Гель-лак DARK №001, білий холодний, 10 мл LILLYSHOP 856 P 0 1 1
18985 Гель-лак DARK №002, чорний, 10 мл LILLYSHOP 856 P 0 1 1
18986 Гель-лак DARK №003, червоний ярко-алий, 10 мл LILLYSHOP 856 P 0 1 1
19418 Гель-лак DARK №004, яскраво-червоний, 10 мл LILLYSHOP 856 P 0 1 1
18988 Гель-лак DARK №005, червоно-малиновий, 10 мл LILLYSHOP 856 P 0 1 1
21627 Гель-лак DARK №006, класичний червоний, 10 мл LILLYSHOP 856 P 0 1 1
18987 Гель-лак DARK №007, насичений червоний, 10 мл LILLYSHOP 856 P 0 1 1
18908 Гель-лак DARK №008, темний червоний, 10 мл LILLYSHOP 856 P 0 1 1
18989 Гель-лак DARK №009, сливово-червоний,10 мл LILLYSHOP 856 P 0 1 1
18990 Гель-лак DARK №010, бордовий, 10 мл LILLYSHOP 856 P 0 1 1
18991 Гель-лак DARK №011, винний, 10 мл LILLYSHOP 856 P 0 1 1
18992 Гель-лак DARK №012, винний бургунді, 10 мл LILLYSHOP 856 P 0 1 1
21628 Гель-лак DARK №013, сливовий, 10 мл LILLYSHOP 856 P 0 1 1
18994 Гель-лак DARK №014, світлий баклажан, 10 мл LILLYSHOP 856 P 0 1 1
21630 Гель-лак DARK №015, баклажан, 10 мл LILLYSHOP 856 P 0 1 1
21629 Гель-лак DARK №016, фіолетовий, 10 мл LILLYSHOP 856 P 0 1 1
21631 Гель-лак DARK №017, темний фіолетовий, 10 мл LILLYSHOP 856 P 0 1 1
21632 Гель-лак DARK №018, пурпурний, 10 мл LILLYSHOP 856 P 0 1 1
18961 Гель-лак DARK №019, синьо-фіолетовий, 10 мл LILLYSHOP 856 P 0 1 1
21634 Гель-лак DARK №020, фіалковий, 10 мл LILLYSHOP 856 P 0 1 1
18995 Гель-лак DARK №021, темний синьо-фіолетовий, 10 мл LILLYSHOP 856 P 0 1 1
19007 Гель-лак DARK №022, темний синій, 10 мл LILLYSHOP 856 P 0 1 1
9348 Гель-лак DARK №023, насичений темно-синій, 10 мл LILLYSHOP 856 P 0 1 1
9347 Гель-лак DARK №024, синій морський, 10 мл LILLYSHOP 856 P 0 1 1
18960 Гель-лак DARK №026, синій ультрамарин, 10 мл LILLYSHOP 856 P 0 1 1
18959 Гель-лак DARK №027, синій, 10 мл LILLYSHOP 856 P 0 1 1
20565 Гель-лак DARK №028, яскраво-синій, 10 мл LILLYSHOP 856 P 0 1 1
18970 Гель-лак DARK №029, сірий, 10 мл LILLYSHOP 856 P 0 1 1
18971 Гель-лак DARK №030, темно-сірий, 10 мл LILLYSHOP 856 P 0 1 1
18973 Гель-лак DARK №032, коричневий, 10 мл LILLYSHOP 856 P 0 1 1
21641 Гель-лак DARK №033, темно-зелений, 10 мл LILLYSHOP 856 P 0 1 1
18975 Гель-лак DARK №034, насичений темно-зелений, 10 мл LILLYSHOP 856 P 0 1 1
18976 Гель-лак DARK №035, насичений зелений, 10 мл LILLYSHOP 856 P 0 1 1
18977 Гель-лак DARK №036, яскраво-зелений, 10 мл LILLYSHOP 856 P 0 1 1
21640 Гель-лак DARK №037, оливковий, 10 мл LILLYSHOP 856 P 0 1 1
22461 Гель-лак DARK №038, гірчичний, 10 мл LILLYSHOP 856 P 0 1 1
21639 Гель-лак DARK №039, світлий бежево-ліловий, 10 мл LILLYSHOP 856 P 0 1 1
18958 Гель-лак DARK №040, натуральний беж, 10 мл LILLYSHOP 856 P 0 1 1
18981 Гель-лак DARK №041, теплий бежевий, 10 мл LILLYSHOP 856 P 0 1 1
18982 Гель-лак DARK №042, бежево-сливовий, 10 мл LILLYSHOP 856 P 0 1 1
21637 Гель-лак DARK №043, рожево-сливовий, 10 мл LILLYSHOP 856 P 0 1 1
18984 Гель-лак DARK №044, темно-малиновий, 10 мл LILLYSHOP 856 P 0 1 1
22048 Гель-лак DARK №045, яскраво-малиновий, 10 мл LILLYSHOP 856 P 0 1 1
22055 Гель-лак DARK №046, яскраво-пурпурний, 10 мл LILLYSHOP 856 P 0 1 1
22052 Гель-лак DARK №047, ліловий, 10 мл LILLYSHOP 856 P 0 1 1
22054 Гель-лак DARK №048, яскравий насичено-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
22064 Гель-лак DARK №049, яскраво-рожевий барбі, 10 мл LILLYSHOP 856 P 0 1 1
22063 Гель-лак DARK №050, яскраво-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
22062 Гель-лак DARK №051, рожевий, 10 мл LILLYSHOP 856 P 0 1 1
22056 Гель-лак DARK №052, кораловий, 10 мл LILLYSHOP 856 P 0 1 1
22061 Гель-лак DARK №053, пастельно-кораловий, 10 мл LILLYSHOP 856 P 0 1 1
22058 Гель-лак DARK №054, пастельний помаранчевий, 10 мл LILLYSHOP 856 P 0 1 1
22044 Гель-лак DARK №055, світло-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
22046 Гель-лак DARK №056, насичений світло-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
22060 Гель-лак DARK №057, зефірний рожевий, 10 мл LILLYSHOP 856 P 0 1 1
18998 Гель-лак DARK №058, пастельно-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
18996 Гель-лак DARK №059, пастельно-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
18997 Гель-лак DARK №060, біло-рожевий айворі, 10 мл LILLYSHOP 856 P 0 1 1
18999 Гель-лак DARK №061, айворі, 10 мл LILLYSHOP 856 P 0 1 1
19000 Гель-лак DARK №062, вершковий айворі, 10 мл LILLYSHOP 856 P 0 1 1
19001 Гель-лак DARK №063, біло-бежевий, 10 мл LILLYSHOP 856 P 0 1 1
19002 Гель-лак DARK №064, біло-ліловий айворі, 10 мл LILLYSHOP 856 P 0 1 1
19003 Гель-лак DARK №065, світло-сірий, 10 мл LILLYSHOP 856 P 0 1 1
19004 Гель-лак DARK №066, світлий сіро-ліловий, 10 мл LILLYSHOP 856 P 0 1 1
19006 Гель-лак DARK №067, ніжна світла лаванда, 10 мл LILLYSHOP 856 P 0 1 1
19005 Гель-лак DARK №068, ніжний світло-ліловий, 10 мл LILLYSHOP 856 P 0 1 1
22051 Гель-лак DARK №069, ніжний сіро-блакитний, 10 мл LILLYSHOP 856 P 0 1 1
22053 Гель-лак DARK №070, світло-бірюзовий, 10 мл LILLYSHOP 856 P 0 1 1
22047 Гель-лак DARK №071, ніжний світло-бірюзовий, 10 мл LILLYSHOP 856 P 0 1 1
22050 Гель-лак DARK №072, небесно-блакитний, 10 мл LILLYSHOP 856 P 0 1 1
22045 Гель-лак DARK №073, блакитний, 10 мл LILLYSHOP 856 P 0 1 1
22479 Гель-лак DARK №074, фіалковий, 10 мл LILLYSHOP 856 P 0 1 1
22049 Гель-лак DARK №075, бірюзовий, 10 мл LILLYSHOP 856 P 0 1 1
22057 Гель-лак DARK №076, світло-жовтий, 10 мл LILLYSHOP 856 P 0 1 1
22059 Гель-лак DARK №077, жовтий, 10 мл LILLYSHOP 856 P 0 1 1
21638 Гель-лак DARK №078, темно-бордовий, 10 мл LILLYSHOP 856 P 0 1 1
21635 Гель-лак DARK №079, стигла вишня, 10 мл LILLYSHOP 856 P 0 1 1
18911 Гель-лак DARK №080, марсала, 10 мл LILLYSHOP 856 P 0 1 1
18912 Гель-лак DARK №081, насичений темно-бордовий, 10 мл LILLYSHOP 856 P 0 1 1
19011 Гель-лак DARK №083, насичений темно-синій, 10 мл LILLYSHOP 856 P 0 1 1
18913 Гель-лак DARK №084, темно-фіолетовий, 10 мл LILLYSHOP 856 P 0 1 1
19066 Гель-лак DARK №085, насичений червоний, 10 мл LILLYSHOP 856 P 0 1 1
21636 Гель-лак DARK №086, яскравий червоний, 10 мл LILLYSHOP 856 P 0 1 1
19013 Гель-лак DARK №087, насичений лососевий, 10 мл LILLYSHOP 856 P 0 1 1
18915 Гель-лак DARK №088, пудровий ліловий, 10 мл LILLYSHOP 856 P 0 1 1
19014 Гель-лак DARK №089, пудровий рожевий, 10 мл LILLYSHOP 856 P 0 1 1
20602 Гель-лак DARK №090, пудровий тепло-ліловий, 10 мл LILLYSHOP 856 P 0 1 1
18917 Гель-лак DARK №091, пудровий світло-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
18918 Гель-лак DARK №092, пильно-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
21642 Гель-лак DARK №093, лілово-рожевий, 10 мл LILLYSHOP 856 P 0 1 1
19016 Гель-лак DARK №095, світлий хакі, 10 мл LILLYSHOP 856 P 0 1 1
18920 Гель-лак DARK №096, світлий сіро-зелений, 10 мл LILLYSHOP 856 P 0 1 1
19017 Гель-лак DARK №097, хакі, 10 мл LILLYSHOP 856 P 0 1 1
20603 Гель-лак DARK №098, помаранчевий, 10 мл LILLYSHOP 856 P 0 1 1
20601 Гель-лак DARK №099, вогняний помаранчевий, 10 мл LILLYSHOP 856 P 0 1 1
18924 Гель-лак DARK №111, 10 мл LILLYSHOP 856 P 0 1 1
18914 Гель-лак DARK №112, темно-вишневий з червоним шиммером, 10 мл LILLYSHOP 856 P 0 1 1
18925 Гель-лак DARK №113, темно-бордовий з золотим шиммером, 10 мл LILLYSHOP 856 P 0 1 1
18926 Гель-лак DARK №114, темно-синій з блакитно-срібним шиммером, 10 мл LILLYSHOP 856 P 0 1 1