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.amount > 0 
    OR products.tracking = 'D'
  ) 
  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 (
    14835, 12786, 17030, 15428, 12787, 12653, 
    12526, 14824, 12971, 15863, 16504, 
    16321, 14396, 15879
  ) 
GROUP BY 
  products.product_id 
ORDER BY 
  NULL asc

Query time 0.05107

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": 79,
            "filtered": 100,
            "index_condition": "variation_group_products.group_id in (14835,12786,17030,15428,12787,12653,12526,14824,12971,15863,16504,16321,14396,15879)"
          }
        },
        {
          "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.amount > 0 or products.tracking = 'D') and (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
16772 999 Lucky Sailboat Model C250.T6 With Calendar HAHAWAVE 12526 V 5479 5.00 1
16771 999 Lucky Sailboat Model C250.T6 With Calendar HAHAWAVE 12526 V 5479 5.00 1
16770 999 Lucky Sailboat Model C250.T6 With Calendar HAHAWAVE 12526 V 5479 5.00 1
16769 999 Lucky Sailboat Model C250.T6 With Calendar HAHAWAVE 12526 V 5479 5.00 1
16768 999 Lucky Sailboat Model C250.T6 With Calendar HAHAWAVE 12526 V 5479 5.00 1
5479 999 Lucky Sailboat Model C250.T6 With Calendar HAHAWAVE 12526 P 0 5.00 1
17984 999 Dark Blue Velvet Handmade Low Heels, 3cm Deep Blue Velvet High Heels, Velvet High Heels, Elegant Velvet Shoes, Gift For Ladies Hoa Tiến Brocade 12653 V 4826 4.00 1
17980 999 Dark Blue Velvet Handmade Low Heels, 3cm Deep Blue Velvet High Heels, Velvet High Heels, Elegant Velvet Shoes, Gift For Ladies Hoa Tiến Brocade 12653 V 4826 4.00 1
17979 999 Dark Blue Velvet Handmade Low Heels, 3cm Deep Blue Velvet High Heels, Velvet High Heels, Elegant Velvet Shoes, Gift For Ladies Hoa Tiến Brocade 12653 V 4826 4.00 1
17978 999 Dark Blue Velvet Handmade Low Heels, 3cm Deep Blue Velvet High Heels, Velvet High Heels, Elegant Velvet Shoes, Gift For Ladies Hoa Tiến Brocade 12653 V 4826 4.00 1
17977 999 Dark Blue Velvet Handmade Low Heels, 3cm Deep Blue Velvet High Heels, Velvet High Heels, Elegant Velvet Shoes, Gift For Ladies Hoa Tiến Brocade 12653 V 4826 4.00 1
4826 999 Dark Blue Velvet Handmade Low Heels, 3cm Deep Blue Velvet High Heels, Velvet High Heels, Elegant Velvet Shoes, Gift For Ladies Hoa Tiến Brocade 12653 P 0 4.00 1
18421 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 V 4268 0
18420 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 V 4268 0
18419 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 V 4268 0
18418 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 V 4268 0
18417 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 V 4268 0
18416 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 V 4268 0
18415 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 V 4268 0
4268 999 Light Brown Derby Brogue Leather Shoes, Derby Leather Shoes For Men, Men's Office Derby Shoes, Low-Collar Men's Leather Shoes Daddy's Shoes 12786 P 0 0
18428 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 V 4267 0
18427 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 V 4267 0
18426 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 V 4267 0
18425 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 V 4267 0
18424 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 V 4267 0
18423 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 V 4267 0
18422 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 V 4267 0
4267 999 Black Derby Brogue Leather Shoes, Cowhide Brogue Derby Shoes, Premium Men's Formal Shoes, Imported Leather Shoes Daddy's Shoes 12787 P 0 0
18946 999 Scented Candles, Scented Wax And Blue Rose Wax Bouquet Gift Box Onio Soapery 12971 V 3668 5.00 3
18945 998 Scented Candles, Scented Wax And Blue Rose Wax Bouquet Gift Box Onio Soapery 12971 V 3668 5.00 3
18944 999 Scented Candles, Scented Wax And Blue Rose Wax Bouquet Gift Box Onio Soapery 12971 V 3668 5.00 3
3668 999 Scented Candles, Scented Wax And Blue Rose Wax Bouquet Gift Box Onio Soapery 12971 P 0 5.00 3
732 50 Brocade Scarf Thổ Cẩm PIÊU - Brocade Decor 14396 P 0 5.00 1
26658 999 Leather Money Clip, Various Colors And Leather Material thanhsimone 14824 P 2247 0
26659 999 Leather Money Clip, Various Colors And Leather Material thanhsimone 14824 P 2247 0
2247 998 Leather Money Clip, Various Colors And Leather Material thanhsimone 14824 P 0 0
26842 999 Half Round Bag, Various Colors And Leather Material thanhsimone 14835 P 2256 0
26843 999 Half Round Bag, Various Colors And Leather Material thanhsimone 14835 P 2256 0
2256 999 Half Round Bag, Various Colors And Leather Material thanhsimone 14835 P 0 0
32306 2 Banana Leaf Ceramic Vase With Heart Shape Opening, Vietnamese Ceramics, Tropical Home Decor, Unique Flower Vase, Handmade Ceramic Vase, Gift Ideas Lina Pottery by Minh Tien Ceramic 15428 P 0 5.00 5
32358 999 Banana Leaf Ceramic Vase With Heart Shape Opening, Vietnamese Ceramics, Tropical Home Decor, Unique Flower Vase, Handmade Ceramic Vase, Gift Ideas Lina Pottery by Minh Tien Ceramic 15428 V 32306 5.00 5
36308 999 Jolie Leather Bag, Round Bottom Design, Compact Design, Durable Cowhide, Elegant Gift, Leather Accessories, Handmade Gifts, Personalized Gift Hoa handmade 15863 P 0 5.00 3
36711 999 Jolie Leather Bag, Round Bottom Design, Compact Design, Durable Cowhide, Elegant Gift, Leather Accessories, Handmade Gifts, Personalized Gift Hoa handmade 15863 V 36308 5.00 3
36712 999 Jolie Leather Bag, Round Bottom Design, Compact Design, Durable Cowhide, Elegant Gift, Leather Accessories, Handmade Gifts, Personalized Gift Hoa handmade 15863 V 36308 5.00 3
36713 999 Jolie Leather Bag, Round Bottom Design, Compact Design, Durable Cowhide, Elegant Gift, Leather Accessories, Handmade Gifts, Personalized Gift Hoa handmade 15863 V 36308 5.00 3
36714 999 Jolie Leather Bag, Round Bottom Design, Compact Design, Durable Cowhide, Elegant Gift, Leather Accessories, Handmade Gifts, Personalized Gift Hoa handmade 15863 V 36308 5.00 3
36715 999 Jolie Leather Bag, Round Bottom Design, Compact Design, Durable Cowhide, Elegant Gift, Leather Accessories, Handmade Gifts, Personalized Gift Hoa handmade 15863 V 36308 5.00 3
36758 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 P 0 5.00 3
36944 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36945 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36946 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36947 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36948 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36949 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36950 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36951 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36952 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36953 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36954 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
36955 999 Jelly No.2 Tealight Scented Candle & Holder Gift Set ADORA 15879 V 36758 5.00 3
40605 999 Glass Diffuser Bottle Flora Collab 16321 V 4191 5.00 3
40606 999 Glass Diffuser Bottle Flora Collab 16321 V 4191 5.00 3
4191 999 Glass Diffuser Bottle Flora Collab 16321 P 0 5.00 3
40611 999 Glass Diffuser Bottle Flora Collab 16321 V 4191 5.00 3
40610 999 Glass Diffuser Bottle Flora Collab 16321 V 4191 5.00 3
40609 999 Glass Diffuser Bottle Flora Collab 16321 V 4191 5.00 3
40608 999 Glass Diffuser Bottle Flora Collab 16321 V 4191 5.00 3
40607 999 Glass Diffuser Bottle Flora Collab 16321 V 4191 5.00 3
41597 999 RAINBOW Sedge Bag, Eye-catching Design, Youthful Style, Highlighted Knitted Handle, Spacious Bag Size, Sturdy Bag Form Kira Craft Vietnam 16504 V 41652 5.00 2
41652 999 RAINBOW Sedge Bag, Eye-catching Design, Youthful Style, Highlighted Knitted Handle, Spacious Bag Size, Sturdy Bag Form Kira Craft Vietnam 16504 P 0 5.00 2
47277 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 P 0 5.00 3
47330 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47321 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47329 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47322 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47323 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47328 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47324 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47327 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47325 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3
47326 999 Name Embroidered Herbal Eye Pillow, Linen Eye Compress Pillow, Hot And Cold Eye Compress, Sleep-Inducing Eye Compress Nhã An Hanoi 17030 V 47277 5.00 3