SELECT 
  cscart_statuses.*, 
  cscart_status_descriptions.* 
FROM 
  cscart_statuses 
  LEFT JOIN cscart_status_descriptions ON cscart_status_descriptions.status_id = cscart_statuses.status_id 
  AND cscart_status_descriptions.lang_code = 'tr' 
WHERE 
  1 = 1 
  AND cscart_statuses.type = 'O' 
ORDER BY 
  cscart_statuses.position asc

Query time 0.00022

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_statuses ALL 19 Using where; Using filesort
1 SIMPLE cscart_status_descriptions eq_ref PRIMARY PRIMARY 9 hobilifedt.cscart_statuses.status_id,const 1 Using where

Result

status_id status type is_default position description email_subj email_header lang_code
1 P O Y 0 Paid has been paid Your order has been paid successfully. tr
2 C O Y 0 Complete has been completed Your order has been completed. Thank you for choosing us. tr
3 O O Y 0 Open has been placed successfully. Thank you for the order. You are welcome to come back! tr
4 F O Y 0 Failed failed Your order has been unsuccessful. Please contact shop administration. tr
5 D O Y 0 Declined has been declined Your order has been declined. Please contact shop administration. tr
6 B O Y 0 Backordered has been backordered Your order has been backordered. tr
7 I O Y 0 Canceled has been canceled Your order has been canceled. Please contact shop administration. tr
19 Y O Y 0 Bekleyen çağrı Bekleyen çağrı Bekleyen çağrı tr