SELECT 
  cscart_statuses.status 
FROM 
  cscart_statuses 
  INNER JOIN cscart_status_data ON cscart_status_data.status_id = cscart_statuses.status_id 
WHERE 
  type = 'O' 
  AND (param, value) IN (
    ('payment_received', 'Y')
  )

Query time 0.00026

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_statuses ALL PRIMARY 19 Using where
1 SIMPLE cscart_status_data eq_ref PRIMARY,inventory PRIMARY 768 hobilifedt.cscart_statuses.status_id,const 1 Using index condition; Using where

Result

status
P
C