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

Query time 0.00044

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
3031 Sito N
3032 Cheka N
6941 Sanz N
3033 One N
3034 Brivi N