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 = 'en' 
  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 = 2 
  AND shared_descr.lang_code = 'en' 
  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 = 'en' 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
WHERE 
  1 
  AND cscart_categories.category_id IN (397) 
  AND cscart_categories.company_id = 2 
  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 
  36, 12

Query time 0.00300

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 70 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
3524 Parquet Ash Milkshake Grande
9985 Parquet Oak Askania Piccolo
10466 Parquet Oak Mainland Chevron 130
9989 Parquet Oak Nugat Piccolo
10463 Parquet Oak Serene Chevron 130
10418 Parquet Oak Trivor Chevron 130
10405 Parquet Oak Salt Chevron 130
10465 Parquet Oak Raisins Chevron 130
10417 Parquet Oak Bright Chevron 130
10385 Parquet Oak Madeira Grande
10464 Parquet Oak Caramel Chevron 130
10373 Parquet Oak Bright Piccolo