Sandbox

SELECT 
  cscart_profile_fields.*, 
  cscart_profile_field_descriptions.description, 
  cscart_profile_fields.profile_required as required 
FROM 
  cscart_profile_fields 
  LEFT JOIN cscart_profile_field_descriptions ON cscart_profile_field_descriptions.object_id = cscart_profile_fields.field_id 
  AND cscart_profile_field_descriptions.object_type = 'F' 
  AND lang_code = 'en' 
WHERE 
  1 = 1 
  AND cscart_profile_fields.profile_type = 'U' 
  AND cscart_profile_fields.profile_show = 'Y' 
  AND (
    cscart_profile_fields.field_type != 'E' 
    OR cscart_profile_fields.section != 'C' 
    AND cscart_profile_fields.field_type = 'E'
  ) 
ORDER BY 
  cscart_profile_fields.position

Query time 0.00358

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5.61"
    },
    "ordering_operation": {
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_profile_fields",
            "access_type": "ref",
            "possible_keys": [
              "profile_show"
            ],
            "key": "profile_show",
            "used_key_parts": [
              "profile_show"
            ],
            "key_length": "4",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 34,
            "rows_produced_per_join": 3,
            "filtered": "9.09",
            "index_condition": "((`s2cart`.`cscart_profile_fields`.`field_type` <> 'E') or (`s2cart`.`cscart_profile_fields`.`field_type` = 'E'))",
            "cost_info": {
              "read_cost": "1.12",
              "eval_cost": "0.31",
              "prefix_cost": "4.53",
              "data_read_per_join": "3K"
            },
            "used_columns": [
              "field_id",
              "field_name",
              "profile_show",
              "profile_required",
              "checkout_show",
              "checkout_required",
              "partner_show",
              "partner_required",
              "storefront_show",
              "field_type",
              "profile_type",
              "position",
              "is_default",
              "section",
              "matching_id",
              "class",
              "wrapper_class",
              "autocomplete_type"
            ],
            "attached_condition": "((`s2cart`.`cscart_profile_fields`.`profile_type` = 'U') and ((`s2cart`.`cscart_profile_fields`.`field_type` <> 'E') or ((`s2cart`.`cscart_profile_fields`.`section` <> 'C') and (`s2cart`.`cscart_profile_fields`.`field_type` = 'E'))))"
          }
        },
        {
          "table": {
            "table_name": "cscart_profile_field_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "object_type",
              "lang_code"
            ],
            "key_length": "12",
            "ref": [
              "s2cart.cscart_profile_fields.field_id",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.77",
              "eval_cost": "0.31",
              "prefix_cost": "5.61",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "object_id",
              "description",
              "object_type",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

field_id	field_name	profile_show	profile_required	checkout_show	checkout_required	partner_show	partner_required	storefront_show	field_type	profile_type	position	is_default	section	matching_id	class	autocomplete_type	description	required
51	GSTN	Y	N	N	N	N	N	Y	T	U	45	N	C	0			GST Number	N
14	b_firstname	Y	N	Y	N	Y	Y	Y	I	U	90	Y	B	15	billing-first-name	given-name	First name	N
15	s_firstname	Y	N	N	N	N	N	Y	I	U	90	Y	S	14	shipping-first-name	given-name	First name	N
16	b_lastname	Y	N	Y	N	Y	Y	Y	I	U	100	Y	B	17	billing-last-name	surname	Last name	N
17	s_lastname	Y	N	N	N	N	N	Y	I	U	100	Y	S	16	shipping-last-name	surname	Last name	N
30	b_phone	Y	Y	Y	N	Y	N	Y	P	U	107	Y	B	31	billing-phone	phone-full	Phone	Y
31	s_phone	Y	Y	N	N	N	N	Y	P	U	107	Y	S	30	shipping-phone	phone-full	Phone	Y
18	b_address	Y	Y	Y	N	Y	Y	Y	I	U	110	Y	B	19	billing-address	street-address	Address	Y
19	s_address	Y	N	Y	Y	N	N	Y	I	U	110	Y	S	18	shipping-address	street-address	Address	N
21	s_address_2	Y	N	N	N	N	N	Y	I	U	120	Y	S	20	shipping-address-line2	address-line2	Address	N
20	b_address_2	Y	Y	N	N	Y	N	Y	I	U	120	Y	B	21	billing-address-line2	address-line2	Address	Y
22	b_city	Y	N	Y	N	Y	Y	Y	I	U	130	Y	B	23	billing-city	city	City	N
23	s_city	Y	N	Y	Y	N	N	Y	I	U	130	Y	S	22	shipping-city	city	City	N
26	b_country	Y	N	Y	N	Y	Y	Y	O	U	140	Y	B	27	billing-country	country	Country	N
27	s_country	Y	N	Y	Y	N	N	Y	O	U	140	Y	S	26	shipping-country	country	Country	N
25	s_state	Y	N	Y	Y	N	N	Y	A	U	150	Y	S	24	shipping-state	state	State/province	N
24	b_state	Y	N	Y	N	Y	Y	Y	A	U	150	Y	B	25	billing-state	state	State/province	N
28	b_zipcode	Y	N	Y	N	Y	Y	Y	Z	U	160	Y	B	29	billing-zip-code	postal-code	Zip/postal code	N
29	s_zipcode	Y	N	Y	Y	N	N	Y	Z	U	160	Y	S	28	shipping-zip-code	postal-code	Zip/postal code	N