SELECT 
  pa.artist_id, 
  a.name, 
  pa.is_remixer 
FROM 
  cscart_label_manager_product_artists pa, 
  cscart_label_manager_artists a 
WHERE 
  a.status = 'A' 
  AND pa.artist_id = a.artist_id 
  AND pa.product_id = 31165 
ORDER BY 
  pa.id ASC

Query time 0.00039

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pa ref product_id,artist_id product_id 3 const 5 Using where
1 SIMPLE a eq_ref PRIMARY,status PRIMARY 3 toolboxdigitalshop_dev.pa.artist_id 1 Using where

Result

artist_id name is_remixer
231 Carl Nicholson N
1190 Grant Molloy N
240 Chrysus N
1505 Stace N
1113 Perfect Poise Y