SELECT 
  * 
FROM 
  cscart_bm_locations as l 
  LEFT JOIN cscart_bm_locations_descriptions as d ON d.location_id = l.location_id 
  AND d.lang_code = 'en' 
WHERE 
  l.layout_id = 1928 
  AND l.is_default = 1 
ORDER BY 
  l.is_default desc

Query time 0.03468

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "9384.88"
    },
    "ordering_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "l",
            "access_type": "ALL",
            "rows_examined_per_scan": 43207,
            "rows_produced_per_join": 432,
            "filtered": "1.00",
            "cost_info": {
              "read_cost": "8779.99",
              "eval_cost": "86.41",
              "prefix_cost": "8866.40",
              "data_read_per_join": "97K"
            },
            "used_columns": [
              "location_id",
              "dispatch",
              "is_default",
              "layout_id",
              "object_ids",
              "custom_html",
              "position"
            ],
            "attached_condition": "((`webmarco`.`l`.`is_default` = 1) and (`webmarco`.`l`.`layout_id` = 1928))"
          }
        },
        {
          "table": {
            "table_name": "d",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "location_id",
              "lang_code"
            ],
            "key_length": "10",
            "ref": [
              "webmarco.l.location_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 432,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "432.07",
              "eval_cost": "86.41",
              "prefix_cost": "9384.88",
              "data_read_per_join": "101K"
            },
            "used_columns": [
              "location_id",
              "lang_code",
              "name",
              "title",
              "meta_description",
              "meta_keywords"
            ],
            "attached_condition": "<if>(is_not_null_compl(d), (`webmarco`.`d`.`location_id` = `webmarco`.`l`.`location_id`), true)"
          }
        }
      ]
    }
  }
}

Result

location_id dispatch is_default layout_id object_ids custom_html position lang_code name title meta_description meta_keywords
56074 default 1 1928 <!-- Hotjar Tracking Code for https://webmarco.com/ --> <script> (function(h,o,t,j,a,r){ h.hj=h.hj||function(){(h.hj.q=h.hj.q||[]).push(arguments)}; h._hjSettings={hjid:2449073,hjsv:6}; a=o.getElementsByTagName('head')[0]; r=o.createElement('script');r.async=1; r.src= "https://static.hotjar.com/c/hotjar-2449073.js?sv=6"; a.appendChild(r); })(window,document); </script> <!-- Global site tag (gtag.js) - Google Analytics --> <script async src="https://www.googletagmanager.com/gtag/js?id=UA-202711900-1"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag("js", new Date()); gtag("config", "UA-202711900-1"); </script> 10 en Default WebMarco - Online Shopping WebMarco - Find EVERYTHING you need, all in one place. webmarco, website, ecommerce, services, buy, sell, offer, business, clothing, electronics, furniture, beauty, toys, construction, car rental, finance, cleaning, personal website, marco order, qr code, kitchen, make up, shoes, books,