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

Query time 0.00035

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 2 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
5750 Adam Taylor N
6780 Charlie Goddard N