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 = 48297 
ORDER BY 
  pa.id ASC

Query time 0.00040

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 4 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
8340 Jedi N
2751 Stormtrooper N
6959 D-Lyte N
5059 Sy & Unknown Y