SELECT 
  a.code, 
  b.state 
FROM 
  cscart_states as a 
  LEFT JOIN cscart_state_descriptions as b ON b.state_id = a.state_id 
  AND b.lang_code = 'ar' 
WHERE 
  a.country_code = 'SA' 
ORDER BY 
  b.state

Query time 0.00059

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "17.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "10.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "a",
            "access_type": "ref",
            "possible_keys": [
              "cs",
              "country_code"
            ],
            "key": "cs",
            "used_key_parts": [
              "country_code"
            ],
            "key_length": "8",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 10,
            "rows_produced_per_join": 10,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.50",
              "eval_cost": "1.00",
              "prefix_cost": "3.50",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "state_id",
              "country_code",
              "code"
            ]
          }
        },
        {
          "table": {
            "table_name": "b",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "state_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "betaatif_store.a.state_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 10,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.50",
              "eval_cost": "1.00",
              "prefix_cost": "7.00",
              "data_read_per_join": "7K"
            },
            "used_columns": [
              "state_id",
              "lang_code",
              "state"
            ]
          }
        }
      ]
    }
  }
}

Result

code state
Eastern المنطقة الشرقية
AlBaha منطقة الباحه
AlJouf منطقة الجوف
Northern منطقة الحدود الشمالية
Riyadh منطقة الرياض
Qassim منطقة القصيم
Madinah منطقة المدينة المنورة
Tabuk منطقة تبوك
Jazan منطقة جازان
Hail منطقة حائل
Asir منطقة عسير
Makkah منطقة مكة المكرمة
Najran منطقة نجران