SELECT 
  cscart_products.product_id, 
  cscart_products.product_code, 
  cscart_products.status, 
  cscart_products.company_id, 
  cscart_products.list_price, 
  cscart_products.shipping_params, 
  cscart_product_review_prepared_data.average_rating, 
  cscart_product_descriptions.product, 
  cscart_product_descriptions.short_description, 
  cscart_product_descriptions.full_description, 
  cscart_product_descriptions.promo_text, 
  IFNULL(
    avg(opr.rating_value), 
    3
  ) as actual_rating, 
  cd.category, 
  cd.category_id, 
  cc.category_id as main_category, 
  pvd.variant_id, 
  pvd.variant, 
  MIN(
    IF(
      cscart_product_prices.percentage_discount = 0, 
      cscart_product_prices.price, 
      cscart_product_prices.price - (
        cscart_product_prices.price * cscart_product_prices.percentage_discount
      )/ 100
    )
  ) as price 
FROM 
  cscart_products 
  LEFT JOIN cscart_product_prices ON cscart_product_prices.product_id = cscart_products.product_id 
  AND cscart_product_prices.lower_limit = 1 
  AND cscart_product_prices.usergroup_id IN (0) 
  LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id 
  LEFT JOIN cscart_product_review_prepared_data ON cscart_product_review_prepared_data.product_id = cscart_products.product_id 
  left join cscart_product_reviews opr on opr.product_id = cscart_products.product_id 
  and opr.status = "A" 
  left join cscart_product_features_values pfv on pfv.product_id = cscart_products.product_id 
  AND pfv.feature_id = 62 
  left join cscart_product_feature_variant_descriptions pvd on pvd.variant_id = pfv.variant_id 
  inner join cscart_products_categories pc on pc.product_id = cscart_products.product_id 
  inner join cscart_categories cc on cc.category_id = pc.category_id 
  and pc.link_type = "M" 
  inner join cscart_category_descriptions cd on cd.category_id = cc.parent_id 
  AND cscart_product_descriptions.lang_code = 'en' 
WHERE 
  cscart_products.product_id = 3966

Query time 0.01008

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "62.62"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "cscart_products",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY",
            "idx_products_inventory"
          ],
          "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": "5K"
          },
          "used_columns": [
            "product_id",
            "product_code",
            "status",
            "company_id",
            "list_price",
            "shipping_params"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_product_prices",
          "access_type": "const",
          "possible_keys": [
            "usergroup",
            "product_id",
            "lower_limit",
            "usergroup_id",
            "idx_product_prices_usergroup_limit",
            "idx_prices_discount_lookup",
            "idx_prices_min_calc",
            "idx_prices_usergroup_lookup",
            "idx_prices_usergroup_product"
          ],
          "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",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "product_id",
            "price",
            "percentage_discount",
            "lower_limit",
            "usergroup_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_product_descriptions",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY",
            "product_id",
            "idx_product_desc_lang",
            "idx_product_name_search",
            "idx_product_desc_sort"
          ],
          "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": "4K"
          },
          "used_columns": [
            "product_id",
            "lang_code",
            "product",
            "short_description",
            "full_description",
            "promo_text"
          ]
        }
      },
      {
        "table": {
          "table_name": "pc",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "link_type",
            "pt",
            "idx_product_category_lookup",
            "idx_products_categories_composite"
          ],
          "key": "idx_products_categories_composite",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 0,
          "filtered": "50.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "0.30",
            "eval_cost": "0.05",
            "prefix_cost": "0.40",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "product_id",
            "category_id",
            "link_type"
          ],
          "attached_condition": "(`cscart_db`.`pc`.`link_type` = 'M')"
        }
      },
      {
        "table": {
          "table_name": "cc",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "parent",
            "p_category_id",
            "idx_categories_parent_status"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "category_id"
          ],
          "key_length": "3",
          "ref": [
            "cscart_db.pc.category_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 0,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.16",
            "eval_cost": "0.05",
            "prefix_cost": "0.61",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "category_id",
            "parent_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "cd",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "category_id"
          ],
          "key_length": "3",
          "ref": [
            "cscart_db.cc.parent_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 0,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.12",
            "eval_cost": "0.05",
            "prefix_cost": "0.79",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "category_id",
            "category"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_product_review_prepared_data",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.27",
            "eval_cost": "0.10",
            "prefix_cost": "1.16",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "product_id",
            "average_rating"
          ]
        }
      },
      {
        "table": {
          "table_name": "opr",
          "access_type": "index_merge",
          "possible_keys": [
            "idx_product_id",
            "idx_product_reviews_status"
          ],
          "key": "intersect(idx_product_id,idx_product_reviews_status)",
          "key_length": "3,3",
          "rows_examined_per_scan": 29,
          "rows_produced_per_join": 29,
          "filtered": "96.67",
          "using_join_buffer": "hash join",
          "cost_info": {
            "read_cost": "21.59",
            "eval_cost": "2.90",
            "prefix_cost": "25.65",
            "data_read_per_join": "47K"
          },
          "used_columns": [
            "product_id",
            "rating_value",
            "status"
          ],
          "attached_condition": "(<if>(found_match(opr), true, true) and <if>(is_not_null_compl(opr), ((`cscart_db`.`opr`.`product_id` = 3966) and (`cscart_db`.`opr`.`status` = 'A')), true))"
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id",
            "fl"
          ],
          "key": "idx_product_feature_variant_id",
          "used_key_parts": [
            "product_id",
            "feature_id"
          ],
          "key_length": "6",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 29,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "17.33",
            "eval_cost": "2.90",
            "prefix_cost": "45.88",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pvd",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id"
          ],
          "key_length": "3",
          "ref": [
            "cscart_db.pfv.variant_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 30,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "13.66",
            "eval_cost": "3.08",
            "prefix_cost": "62.62",
            "data_read_per_join": "230K"
          },
          "used_columns": [
            "variant_id",
            "variant"
          ]
        }
      }
    ]
  }
}