SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  products.amount, 
  descr1.product as product, 
  companies.company as company_name, 
  variation_group_products.group_id AS variation_group_id, 
  products.product_type, 
  products.parent_product_id, 
  cscart_product_review_prepared_data.average_rating average_rating, 
  cscart_product_review_prepared_data.reviews_count product_reviews_count 
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_companies AS companies ON companies.company_id = products.company_id 
  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 
  INNER JOIN cscart_product_variation_group_products AS variation_group_products ON variation_group_products.product_id = products.product_id 
  LEFT JOIN cscart_product_review_prepared_data ON cscart_product_review_prepared_data.product_id = products.product_id 
  AND cscart_product_review_prepared_data.storefront_id = 0 
WHERE 
  1 
  AND companies.status IN ('A') 
  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 variation_group_products.group_id IN (
    16465, 15473, 15474, 15472, 15475, 12314
  ) 
GROUP BY 
  products.product_id 
ORDER BY 
  NULL asc

Query time 0.01109

JSON explain

{
  "query_block": {
    "select_id": 1,
    "temporary_table": {
      "nested_loop": [
        {
          "table": {
            "table_name": "variation_group_products",
            "access_type": "range",
            "possible_keys": ["PRIMARY", "idx_group_id"],
            "key": "idx_group_id",
            "key_length": "3",
            "used_key_parts": ["group_id"],
            "rows": 18,
            "filtered": 100,
            "index_condition": "variation_group_products.group_id in (16465,15473,15474,15472,15475,12314)"
          }
        },
        {
          "table": {
            "table_name": "products",
            "access_type": "eq_ref",
            "possible_keys": ["PRIMARY", "status"],
            "key": "PRIMARY",
            "key_length": "3",
            "used_key_parts": ["product_id"],
            "ref": ["dev_db.variation_group_products.product_id"],
            "rows": 1,
            "filtered": 51.89157104,
            "attached_condition": "(products.usergroup_ids = '' or find_in_set(0,products.usergroup_ids) or find_in_set(1,products.usergroup_ids)) and products.`status` = 'A'"
          }
        },
        {
          "table": {
            "table_name": "companies",
            "access_type": "eq_ref",
            "possible_keys": ["PRIMARY"],
            "key": "PRIMARY",
            "key_length": "4",
            "used_key_parts": ["company_id"],
            "ref": ["dev_db.products.company_id"],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "companies.`status` = 'A'"
          }
        },
        {
          "table": {
            "table_name": "descr1",
            "access_type": "eq_ref",
            "possible_keys": ["PRIMARY", "product_id"],
            "key": "PRIMARY",
            "key_length": "11",
            "used_key_parts": ["product_id", "lang_code"],
            "ref": ["dev_db.variation_group_products.product_id", "const"],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "trigcond(descr1.lang_code = 'en')"
          }
        },
        {
          "table": {
            "table_name": "cscart_product_review_prepared_data",
            "access_type": "eq_ref",
            "possible_keys": ["PRIMARY"],
            "key": "PRIMARY",
            "key_length": "7",
            "used_key_parts": ["product_id", "storefront_id"],
            "ref": ["dev_db.variation_group_products.product_id", "const"],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "products_categories",
            "access_type": "ref",
            "possible_keys": ["PRIMARY", "pt"],
            "key": "pt",
            "key_length": "3",
            "used_key_parts": ["product_id"],
            "ref": ["dev_db.variation_group_products.product_id"],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "eq_ref",
            "possible_keys": ["PRIMARY", "p_category_id"],
            "key": "PRIMARY",
            "key_length": "3",
            "used_key_parts": ["category_id"],
            "ref": ["dev_db.products_categories.category_id"],
            "rows": 1,
            "filtered": 100,
            "using_index": true
          }
        }
      ]
    }
  }
}

Result

product_id amount product company_name variation_group_id product_type parent_product_id average_rating product_reviews_count
15600 999 Sencha Brown Rice Tea, Healthy Tea, Natural Ingredients, Green Tea, Sencha, Brown Rice, No Preservatives, Gift For Health Hygie and Panacee 12314 V 6063 5.00 9
6063 999 Sencha Brown Rice Tea, Healthy Tea, Natural Ingredients, Green Tea, Sencha, Brown Rice, No Preservatives, Gift For Health Hygie and Panacee 12314 P 0 5.00 9
32618 999 Solanum Procumbens Instant Tea, Solanum Procumbens, Herbal Instant Tea, Vietnamese Herbal, Natural Ingredients, Good For Liver, Healthy Tea Hygie and Panacee 15472 V 6058 4.89 9
6058 999 Solanum Procumbens Instant Tea, Solanum Procumbens, Herbal Instant Tea, Vietnamese Herbal, Natural Ingredients, Good For Liver, Healthy Tea Hygie and Panacee 15472 P 0 4.89 9
32619 999 Solanum Procumbens Instant Tea, Solanum Procumbens, Herbal Instant Tea, Vietnamese Herbal, Natural Ingredients, Good For Liver, Healthy Tea Hygie and Panacee 15472 V 6058 4.89 9
32620 998 Gymnema Sylvestre Instant Tea, Herbal Tea, Vietnamese Herbal, All-Natural Tea, Vietnamese Tea, Healthy Instant Tea, Convenient, Tea Gift Hygie and Panacee 15473 V 6059 4.69 13
6059 999 Gymnema Sylvestre Instant Tea, Herbal Tea, Vietnamese Herbal, All-Natural Tea, Vietnamese Tea, Healthy Instant Tea, Convenient, Tea Gift Hygie and Panacee 15473 P 0 4.69 13
32621 999 Gymnema Sylvestre Instant Tea, Herbal Tea, Vietnamese Herbal, All-Natural Tea, Vietnamese Tea, Healthy Instant Tea, Convenient, Tea Gift Hygie and Panacee 15473 V 6059 4.69 13
32622 999 Daisy Instant Tea, Vietnamese Herbal Tea, Natural Tea, Calming Tea, Sleep Aid Tea, Convenient Instant Tea, Gift Ideas, Thoughtful Gift, Tea Gift Hygie and Panacee 15474 V 6060 5.00 9
6060 999 Daisy Instant Tea, Vietnamese Herbal Tea, Natural Tea, Calming Tea, Sleep Aid Tea, Convenient Instant Tea, Gift Ideas, Thoughtful Gift, Tea Gift Hygie and Panacee 15474 P 0 5.00 9
32623 999 Daisy Instant Tea, Vietnamese Herbal Tea, Natural Tea, Calming Tea, Sleep Aid Tea, Convenient Instant Tea, Gift Ideas, Thoughtful Gift, Tea Gift Hygie and Panacee 15474 V 6060 5.00 9
32624 999 Winter Melon Instant Tea, Herbal Instant Tea, Detox Drinks, Reduce Heat, Healthy Drinks, Organic Tea, No Preservatives Hygie and Panacee 15475 V 6061 5.00 7
6061 999 Winter Melon Instant Tea, Herbal Instant Tea, Detox Drinks, Reduce Heat, Healthy Drinks, Organic Tea, No Preservatives Hygie and Panacee 15475 P 0 5.00 7
32625 999 Winter Melon Instant Tea, Herbal Instant Tea, Detox Drinks, Reduce Heat, Healthy Drinks, Organic Tea, No Preservatives Hygie and Panacee 15475 V 6061 5.00 7
41308 999 Perilla Instant Tea, Herbal Instant Tea, Vietnamese Herbs, Natural, Good For Sleep, Improve Resistance, Convenience, Office Friendly Hygie and Panacee 16465 V 41279 4.64 14
41307 999 Perilla Instant Tea, Herbal Instant Tea, Vietnamese Herbs, Natural, Good For Sleep, Improve Resistance, Convenience, Office Friendly Hygie and Panacee 16465 V 41279 4.64 14
41279 999 Perilla Instant Tea, Herbal Instant Tea, Vietnamese Herbs, Natural, Good For Sleep, Improve Resistance, Convenience, Office Friendly Hygie and Panacee 16465 P 0 4.64 14