SELECT 
  cscart_payments.*, 
  cscart_payment_descriptions.*, 
  IF (
    ISNULL(cscart_addons.status), 
    "A", 
    cscart_addons.status
  ) AS processor_status, 
  cscart_payment_processors.type AS processor_type, 
  cscart_payment_processors.processor_script AS processor_script 
FROM 
  cscart_payments 
  LEFT JOIN cscart_payment_descriptions ON cscart_payment_descriptions.payment_id = cscart_payments.payment_id 
  AND cscart_payment_descriptions.lang_code = 'tr' 
  LEFT JOIN cscart_payment_processors ON cscart_payment_processors.processor_id = cscart_payments.processor_id 
  LEFT JOIN cscart_addons ON cscart_payment_processors.addon = cscart_addons.addon 
  LEFT JOIN cscart_storefronts_payments AS storefronts_payments ON storefronts_payments.payment_id = cscart_payments.payment_id 
WHERE 
  cscart_payments.status = 'A' 
  AND (
    cscart_payments.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_payments.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_payments.usergroup_ids
    )
  ) 
  AND (
    storefronts_payments.storefront_id = 1 
    OR storefronts_payments.storefront_id IS NULL
  ) 
HAVING 
  processor_status = 'A' 
ORDER BY 
  cscart_payments.position

Query time 0.00082

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE storefronts_payments system PRIMARY,idx_storefront_id 0 const row not found
1 SIMPLE cscart_payments ALL c_status 8 Using where; Using filesort
1 SIMPLE cscart_payment_descriptions eq_ref PRIMARY PRIMARY 9 hobilifedt.cscart_payments.payment_id,const 1 Using where
1 SIMPLE cscart_payment_processors eq_ref PRIMARY PRIMARY 3 hobilifedt.cscart_payments.processor_id 1
1 SIMPLE cscart_addons eq_ref PRIMARY PRIMARY 98 hobilifedt.cscart_payment_processors.addon 1 Using where

Result

payment_id company_id usergroup_ids position status template processor_id processor_params a_surcharge p_surcharge tax_ids localization payment_category payment description instructions surcharge_title lang_code processor_status processor_type processor_script
1 0 0 10 A views/orders/components/payments/cc_outside.tpl 106 a:3:{s:20:"success_order_status";s:1:"P";s:20:"failure_order_status";s:1:"F";s:15:"iyzico_response";s:1:"Y";} 0.000 0.000 tab1 Credit card Visa, Mastercard, etc... tr A P wk_hipay.php
2 0 0 40 A views/orders/components/payments/phone.tpl 0 0.000 0.000 tab3 Phone ordering Call 555-0123 <h2>Telefonla siparişi ne zaman kullanmalıyım?</h2> <p>Telefonla sipariş, kredi kartı gibi normal ödeme yöntemlerinin kullanılamadığı veya güvenli olmadığı düşünüldüğünde kullanışlıdır.</p> <p>Operatörlerimiz siparişinizi her iş günü sabah 9'dan akşam 5'e kadar telefonla almaktan mutluluk duyacaktır.</p> tr A