Not sure what this does. However, it is in the slow query list.
SELECT count( DISTINCT b.product_id)
  FROM prfxx_hikashop_product_category AS a
  LEFT JOIN prfxx_hikashop_product AS b
  ON a.product_id=b.product_id
  WHERE b.product_published=1
  AND b.product_type = 'main'
  AND a.category_id IN (we have 2000+ categories and every category id is listed here)
  AND b.product_quantity!=0
  AND (b.product_access = 'all' OR b.product_access LIKE '%,1,%')
  ORDER BY a.ordering DESC

Best Sellers (4 returned)
SELECT DISTINCT b.*
  FROM prfxx_hikashop_product_category AS a
  LEFT JOIN prfxx_hikashop_product AS b
  ON a.product_id=b.product_id
  WHERE b.product_published=1
  AND b.product_type = 'main'
  AND a.category_id IN (we have 2000+ categories and every category id is listed here)
  AND b.product_quantity!=0
  AND (b.product_access = 'all' OR b.product_access LIKE '%,1,%')
  ORDER BY a.ordering DESC
  LIMIT 0, 4


Random Product (1 returned)
SELECT DISTINCT b.*
  FROM prfxx_hikashop_product_category AS a
  LEFT JOIN prfxx_hikashop_product AS b
  ON a.product_id=b.product_id
  WHERE b.product_published=1
  AND b.product_type = 'main'
  AND a.category_id IN (we have 2000+ categories and every category id is listed here)
  AND b.product_quantity!=0
  AND (b.product_access = 'all' OR b.product_access LIKE '%,1,%')
  ORDER BY RAND()
  LIMIT 0, 1