SELECT 
  * 
FROM 
  cscart_product_bundles as bundles 
  LEFT JOIN cscart_product_bundle_descriptions as descriptions ON bundles.bundle_id = descriptions.bundle_id 
  AND descriptions.lang_code = 'en' 
  LEFT JOIN cscart_product_bundle_product_links as links ON bundles.bundle_id = links.bundle_id 
WHERE 
  1 
  AND links.product_id = 31827 
  AND links.show_on_product_page = 'Y' 
  AND bundles.parent_bundle_id = 0 
  AND bundles.status = 'A' 
LIMIT 
  0, 20

Query time 0.00030

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.52"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "links",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "bundle_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 0,
          "filtered": "33.33",
          "index_condition": "(`cscart_db`.`links`.`bundle_id` is not null)",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.03",
            "prefix_cost": "0.35",
            "data_read_per_join": "5"
          },
          "used_columns": [
            "bundle_id",
            "product_id",
            "amount",
            "show_on_product_page"
          ],
          "attached_condition": "(`cscart_db`.`links`.`show_on_product_page` = 'Y')"
        }
      },
      {
        "table": {
          "table_name": "bundles",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "bundle_id"
          ],
          "key_length": "4",
          "ref": [
            "cscart_db.links.bundle_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 0,
          "filtered": "50.00",
          "cost_info": {
            "read_cost": "0.08",
            "eval_cost": "0.02",
            "prefix_cost": "0.47",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "bundle_id",
            "parent_bundle_id",
            "company_id",
            "products",
            "date_from",
            "date_to",
            "display_in_promotions",
            "status",
            "linked_promotion_id"
          ],
          "attached_condition": "((`cscart_db`.`bundles`.`parent_bundle_id` = 0) and (`cscart_db`.`bundles`.`status` = 'A'))"
        }
      },
      {
        "table": {
          "table_name": "descriptions",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "bundle_id",
            "lang_code"
          ],
          "key_length": "10",
          "ref": [
            "cscart_db.links.bundle_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 0,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.04",
            "eval_cost": "0.02",
            "prefix_cost": "0.53",
            "data_read_per_join": "306"
          },
          "used_columns": [
            "bundle_id",
            "name",
            "storefront_name",
            "description",
            "lang_code",
            "full_description"
          ]
        }
      }
    ]
  }
}