SELECT 
  tconnect_ab__gp_locations.*, 
  tconnect_ab__gp_location_descriptions.seo_name 
FROM 
  tconnect_ab__gp_locations 
  LEFT JOIN tconnect_ab__gp_location_descriptions ON tconnect_ab__gp_locations.location_id = tconnect_ab__gp_location_descriptions.location_id 
WHERE 
  1 
  AND tconnect_ab__gp_location_descriptions.seo_name = 'ab-stock-buy-a-promotional-hair-dryer-valera-get-a-40-discount-on-the-curling-iron.html' 
  AND tconnect_ab__gp_locations.status = 'A'

Query time 0.00215

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.003506245,
    "nested_loop": [
      {
        "table": {
          "table_name": "tconnect_ab__gp_location_descriptions",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "seo_name"],
          "key": "seo_name",
          "key_length": "302",
          "used_key_parts": ["seo_name"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.001792605,
          "filtered": 100,
          "attached_condition": "tconnect_ab__gp_location_descriptions.seo_name = 'ab-stock-buy-a-promotional-hair-dryer-valera-get-a-40-discount-on-the-curling-iron.html'",
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "tconnect_ab__gp_locations",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["location_id"],
          "ref": ["tconnect.tconnect_ab__gp_location_descriptions.location_id"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00171364,
          "filtered": 100,
          "attached_condition": "tconnect_ab__gp_locations.`status` = 'A' and tconnect_ab__gp_locations.location_id = tconnect_ab__gp_location_descriptions.location_id"
        }
      }
    ]
  }
}