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 = 'tr' 
WHERE 
  1 = 1 
  AND cscart_profile_fields.profile_type = 'S' 
  AND cscart_profile_fields.profile_show = 'Y' 
ORDER BY 
  cscart_profile_fields.position

Query time 0.00147

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_profile_fields ref profile_show profile_show 4 const 15 Using index condition; Using where; Using filesort
1 SIMPLE cscart_profile_field_descriptions eq_ref PRIMARY PRIMARY 12 hobilifedt.cscart_profile_fields.field_id,const,const 1 Using where

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 wrapper_class autocomplete_type description required
36 company Y Y N N N N Y I S 10 Y C 0 company organization Firma Ünvanı Y
50 tax_number Y N N N N N N I S 14 Y C 0 tax_number Tax number N
51 plan_id Y N N N N N Y V S 15 Y C 0 plan-id Plan N
37 admin_firstname Y Y N N N N N I S 20 N C 0 first-name given-name Ad Y
38 admin_lastname Y Y N N N N N I S 30 N C 0 last-name surname Soyad Y
39 email Y Y N N N N Y E S 40 Y C 0 email email E-posta Y