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 (17317, 17318, 17319, 17320, 17321) 
GROUP BY 
  products.product_id 
ORDER BY 
  NULL asc

Query time 0.02824

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": 97,
            "filtered": 100,
            "index_condition": "variation_group_products.group_id in (17317,17318,17319,17320,17321)"
          }
        },
        {
          "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
49864 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49865 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49866 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49873 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 P 0 5.00 18
49872 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49871 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49870 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49869 995 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
40818 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49874 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49875 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49876 996 Lavender Eye Pillow, Herbal Eye Compress Pillow, Eye Relaxation Compress Bag, Hot And Cold Compress Pillow, Eye Mask Pillow Springhill 17317 V 49873 5.00 18
49879 3 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49877 2 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49878 1 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49880 2 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
40824 2 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 P 0 5.00 13
49881 0 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49882 2 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49883 2 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49884 1 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49885 3 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49886 2 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49887 1 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49888 5 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49889 3 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49890 4 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49891 5 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49892 3 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49893 4 Rosemary Eye Pillow, Herbal Compress Pillow, Eye Relaxation Compress Pillow, Personalized Embroidered Pillow, Hot And Cold Compress Springhill 17318 V 40824 5.00 13
49895 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 P 0 0
49896 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49897 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49898 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49899 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49900 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49901 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49902 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49903 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49904 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49905 1000 Personalized Embroidered Herbal Pillow, Pain Relief, Multi-Purpose, Neck And Shoulder Heating Pad, Hot and Cold Compress, Personalized Gifts Springhill 17319 V 49895 0
49906 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49907 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49908 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49909 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49910 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49911 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49914 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49915 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49916 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 P 0 0
49917 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49918 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49919 100 "Care Box" Gift Set, Eye Pillow And Pillow Mist, Personalized Embroidery, Natural Ingredients, Aromatherapy, Health Gift Box, Elegant Gift Boxes Springhill 17320 V 49916 0
49920 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49921 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49922 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49923 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49924 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 P 0 0
49925 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49926 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49927 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49928 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0
49929 100 2-In-1 Herbal Eye Pillow, Personalized Embroidered, Natural Ingredients, Adjustable Strap, Relaxing Eye Mask, Hot and Cold Eye Pillow, Personalized Gifts Springhill 17321 V 49924 0