Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.product, descr1.product
  ) as product, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.youtube_link, products.youtube_link
  ) AS youtube_link, 
  cscart_aval_statuses_descr.descr as aval_descr 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'de' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id 
  AND shared_descr.company_id = 1 
  AND shared_descr.lang_code = 'de' 
  LEFT JOIN cscart_aval_statuses ON cscart_aval_statuses.aval_status_id = products.aval_status 
  LEFT JOIN cscart_aval_statuses_descr ON cscart_aval_statuses.aval_status_id = cscart_aval_statuses_descr.aval_status_id 
  AND cscart_aval_statuses.status = 'A' 
  AND cscart_aval_statuses_descr.lang_code = 'de' 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
WHERE 
  1 
  AND cscart_categories.category_id IN (443) 
  AND cscart_categories.company_id = 1 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND products.status IN ('A') 
  AND prices.usergroup_id IN (0, 0, 1) 
GROUP BY 
  products.product_id 
ORDER BY 
  popularity.total desc, 
  product_id ASC 
LIMIT 
  0, 12

Query time 0.00151

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	cscart_categories	const	PRIMARY,c_status,p_category_id,idx_category_id	PRIMARY	3	const	1	Using temporary; Using filesort
1	SIMPLE	products_categories	ref	PRIMARY,pt,product_id_idx,category_id_idx,product_category_idx,category_product_idx	PRIMARY	3	const	128	Using where; Using index
1	SIMPLE	products	eq_ref	PRIMARY,status,product_id_idx,status_idx	PRIMARY	3	mahm3t_cs443.products_categories.product_id	1	Using where
1	SIMPLE	prices	ref	usergroup,product_id,lower_limit,usergroup_id	usergroup	3	mahm3t_cs443.products_categories.product_id	1	Using where; Using index
1	SIMPLE	shared_descr	eq_ref	PRIMARY,product_id,company_id	PRIMARY	13	mahm3t_cs443.products_categories.product_id,const,const	1	Using where
1	SIMPLE	cscart_aval_statuses	eq_ref	PRIMARY	PRIMARY	4	mahm3t_cs443.products.aval_status	1
1	SIMPLE	cscart_aval_statuses_descr	ref	aval_status_id	aval_status_id	10	mahm3t_cs443.cscart_aval_statuses.aval_status_id,const	1	Using where
1	SIMPLE	popularity	eq_ref	PRIMARY,total	PRIMARY	3	mahm3t_cs443.products_categories.product_id	1
1	SIMPLE	descr1	eq_ref	PRIMARY,product_id	PRIMARY	9	mahm3t_cs443.products_categories.product_id,const	1	Using where

Result

product_id	product	youtube_link	aval_descr
443	Extrudiertes Polystyrol, XPS 30 L, Perimeterdämmung
563	EPS Dämmplatte Dach und Boden STANDARD
566	EPS Dämmplatte Dach und Boden THERMO aus Neopor
564	EPS Dämmplatte Dach und Boden SILVER
10218	EPS Dämmplatte Dach und Boden THERMO PLUS aus Neopor
565	EPS Dämmplatte Dach und Boden GOLD
567	EPS Dämmplatte Parkplatz THERMO
10350	EPS Dämmplatte Parkplatz THERMO+