SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  products.product_type, 
  products.parent_product_id, 
  products.master_product_offers_count, 
  products.master_product_id, 
  products.company_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(
      IF(
        products.tracking = 'O', 
        (
          SELECT 
            MAX(amount) 
          FROM 
            cscart_product_options_inventory s_inventory 
          WHERE 
            s_inventory.product_id = products.product_id
        ) > 0, 
        products.amount > 0
      ), 
      1, 
      IF(products.tracking = 'D', 1, 0)
    )
  ) as instock_sorting, 
  descr1.ab__custom_product_h1 
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 
  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 
  LEFT JOIN cscart_warehouses_sum_products_amount as war_sum_amount ON war_sum_amount.product_id = products.product_id 
WHERE 
  1 
  AND prices.usergroup_id IN (0) 
  AND (
    (
      1 
      AND products.product_id IN (276421)
    ) 
    AND prices.usergroup_id IN (0)
  ) 
GROUP BY 
  products.product_id 
ORDER BY 
  instock_sorting DESC, 
  NULL asc

Query time 0.00199

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.45"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "war_sum_amount",
              "access_type": "system",
              "possible_keys": [
                "PRIMARY"
              ],
              "rows_examined_per_scan": 0,
              "rows_produced_per_join": 1,
              "filtered": "0.00",
              "const_row_not_found": true,
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.10",
                "prefix_cost": "0.00",
                "data_read_per_join": "16"
              },
              "used_columns": [
                "product_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.10",
                "prefix_cost": "0.00",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "company_id",
                "amount",
                "tracking",
                "parent_product_id",
                "master_product_id",
                "master_product_offers_count"
              ]
            }
          },
          {
            "table": {
              "table_name": "descr1",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY",
                "product_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "const",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.10",
                "prefix_cost": "0.00",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product",
                "ab__custom_product_h1"
              ]
            }
          },
          {
            "table": {
              "table_name": "prices",
              "access_type": "const",
              "possible_keys": [
                "usergroup",
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "key": "usergroup",
              "used_key_parts": [
                "product_id",
                "usergroup_id",
                "lower_limit"
              ],
              "key_length": "9",
              "ref": [
                "const",
                "const",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.10",
                "prefix_cost": "0.00",
                "data_read_per_join": "24"
              },
              "used_columns": [
                "product_id",
                "lower_limit",
                "usergroup_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "pt",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.25",
                "eval_cost": "0.10",
                "prefix_cost": "0.35",
                "data_read_per_join": "16"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "s2cart.products_categories.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.10",
                "prefix_cost": "0.45",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "category_id"
              ]
            }
          }
        ],
        "optimized_away_subqueries": [
          {
            "dependent": true,
            "cacheable": false,
            "query_block": {
              "select_id": 3,
              "message": "no matching row in const table"
            }
          },
          {
            "dependent": true,
            "cacheable": false,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "0.54"
              },
              "table": {
                "table_name": "cscart_products",
                "access_type": "ref",
                "possible_keys": [
                  "idx_parent_product_id"
                ],
                "key": "idx_parent_product_id",
                "used_key_parts": [
                  "parent_product_id"
                ],
                "key_length": "4",
                "ref": [
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 1,
                "filtered": "100.00",
                "index_condition": "(`s2cart`.`cscart_products`.`parent_product_id` = '276421')",
                "cost_info": {
                  "read_cost": "0.44",
                  "eval_cost": "0.10",
                  "prefix_cost": "0.54",
                  "data_read_per_join": "7K"
                },
                "used_columns": [
                  "amount",
                  "parent_product_id"
                ]
              }
            }
          }
        ]
      }
    }
  }
}