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 (
    14525, 13237, 14675, 12785, 12784, 12176, 
    12970, 14226, 14635, 16953, 15746, 
    13450, 12891, 14836, 13656, 12791, 
    16931, 17031, 12902
  ) 
GROUP BY 
  products.product_id 
ORDER BY 
  NULL asc

Query time 0.07040

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": 102,
            "filtered": 100,
            "index_condition": "variation_group_products.group_id in (14525,13237,14675,12785,12784,12176,12970,14226,14635,16953,15746,13450,12891,14836,13656,12791,16931,17031,12902)"
          }
        },
        {
          "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
14920 999 Kapok Zabuton And Mung Bean Zafu Meditation Cushion Set, Blended Color Collection La Touche 12176 V 6557 5.00 1
14919 999 Kapok Zabuton And Mung Bean Zafu Meditation Cushion Set, Blended Color Collection La Touche 12176 V 6557 5.00 1
14918 999 Kapok Zabuton And Mung Bean Zafu Meditation Cushion Set, Blended Color Collection La Touche 12176 V 6557 5.00 1
14917 999 Kapok Zabuton And Mung Bean Zafu Meditation Cushion Set, Blended Color Collection La Touche 12176 V 6557 5.00 1
14916 999 Kapok Zabuton And Mung Bean Zafu Meditation Cushion Set, Blended Color Collection La Touche 12176 V 6557 5.00 1
6557 999 Kapok Zabuton And Mung Bean Zafu Meditation Cushion Set, Blended Color Collection La Touche 12176 P 0 5.00 1
18407 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 V 4270 5.00 1
18406 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 V 4270 5.00 1
18405 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 V 4270 5.00 1
18404 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 V 4270 5.00 1
18403 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 V 4270 5.00 1
18402 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 V 4270 5.00 1
18401 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 V 4270 5.00 1
4270 999 Spectator Derby Brogue Leather Shoes, Olive & Light Brown, Spectator Leather Shoes For Men, Unique Style Shoes, Genuine Leather Shoes Daddy's Shoes 12784 P 0 5.00 1
18414 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 V 4269 5.00 2
18413 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 V 4269 5.00 2
18412 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 V 4269 5.00 2
18411 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 V 4269 5.00 2
18410 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 V 4269 5.00 2
18409 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 V 4269 5.00 2
18408 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 V 4269 5.00 2
4269 999 Brown-Gray Derby Brogue Leather Shoes, Dusty Men's Shoes, Brown Wax Cowhide Leather Shoes, Derby Shoes For Men, Brown Wax Leather Shoes Daddy's Shoes 12785 P 0 5.00 2
18456 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 V 4263 5.00 3
18455 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 V 4263 5.00 3
18454 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 V 4263 5.00 3
18453 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 V 4263 5.00 3
18452 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 V 4263 5.00 3
18451 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 V 4263 5.00 3
18450 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 V 4263 5.00 3
4263 999 Casual Black Leather Boots, Black Boots, Non-Slip Rubber Sole Boots, Workwear Style Leather Shoes, Handmade Leather Shoes Daddy's Shoes 12791 P 0 5.00 3
18731 999 Vara Ramis Clutch Tomato Handmade 12891 V 3975 5.00 1
18730 999 Vara Ramis Clutch Tomato Handmade 12891 V 3975 5.00 1
18729 999 Vara Ramis Clutch Tomato Handmade 12891 V 3975 5.00 1
3975 999 Vara Ramis Clutch Tomato Handmade 12891 P 0 5.00 1
18748 999 Maa Cushion, Cotton & Hemp Fabric Mix, Minimalist Design, Easy to Clean, Natural Material, Durable Product Hemp Oi 12902 P 0 5.00 2
18931 999 Unique Scented Candle Gift Box Onio Soapery 12970 V 3669 5.00 2
18930 999 Unique Scented Candle Gift Box Onio Soapery 12970 V 3669 5.00 2
3669 999 Unique Scented Candle Gift Box Onio Soapery 12970 P 0 5.00 2
20553 999 3D Customized Photo Frame, 100% Pine Wood, Custom Design Images, Pre-Approved Before Shipment, Handmade Product Mood Up 13237 V 3004 5.00 10
3004 999 3D Customized Photo Frame, 100% Pine Wood, Custom Design Images, Pre-Approved Before Shipment, Handmade Product Mood Up 13237 P 0 5.00 10
21085 7 Cinnamon Scented Candle with Wooden Wick, Glass Jar, Vegetable Wax, Perfect Accent for Any Space, Meaningful Gift Iris Nguyen - Perfumery House 13450 V 2457 5.00 1
2457 10 Cinnamon Scented Candle with Wooden Wick, Glass Jar, Vegetable Wax, Perfect Accent for Any Space, Meaningful Gift Iris Nguyen - Perfumery House 13450 P 0 5.00 6
22062 999 White Oud Perfume, Long-Lasting Women's Perfume, Intense And Sweet Fragrance, Compact Design, Gift For Women Saigon Elixir 13656 V 2019 5.00 4
22061 999 White Oud Perfume, Long-Lasting Women's Perfume, Intense And Sweet Fragrance, Compact Design, Gift For Women Saigon Elixir 13656 V 2019 5.00 4
2019 999 White Oud Perfume, Long-Lasting Women's Perfume, Intense And Sweet Fragrance, Compact Design, Gift For Women Saigon Elixir 13656 P 0 5.00 4
24142 1998 Macrame Bag With Patterns And Wooden Handle, Impressive And Eye-Catching Color Combination, Unique Accessory, A Highlight For The Outfit Ori Handicraft 14226 V 1041 5.00 1
24141 1998 Macrame Bag With Patterns And Wooden Handle, Impressive And Eye-Catching Color Combination, Unique Accessory, A Highlight For The Outfit Ori Handicraft 14226 V 1041 5.00 1
1041 1998 Macrame Bag With Patterns And Wooden Handle, Impressive And Eye-Catching Color Combination, Unique Accessory, A Highlight For The Outfit Ori Handicraft 14226 P 0 5.00 1
25182 1018 Dried Flowers Rectangle & Hexagonal Wooden Frame, Meticulously Arranged Dried Flowers, Small Garden Full of Flowers, Decorative Picture Flora Collab 14525 V 25180 5.00 5
25180 1017 Dried Flowers Rectangle & Hexagonal Wooden Frame, Meticulously Arranged Dried Flowers, Small Garden Full of Flowers, Decorative Picture Flora Collab 14525 P 0 5.00 5
25181 1018 Dried Flowers Rectangle & Hexagonal Wooden Frame, Meticulously Arranged Dried Flowers, Small Garden Full of Flowers, Decorative Picture Flora Collab 14525 V 25180 5.00 5
25850 999 Wallet With Buttons, Minimalist and Elegant Design, Premium Herbal Leather Material, Luxurious Style, Compact Size, High-quality Product thanhsimone 14635 P 0 5.00 1
294 1998 Keychain With Feng Shui Crystal, Handmade Macrame Cord, Natural Stones, Dispels Negative Energy, Healing Stones, Spiritual Accessory Crystals Macrame 14675 P 0 5.00 1
26109 1998 Keychain With Feng Shui Crystal, Handmade Macrame Cord, Natural Stones, Dispels Negative Energy, Healing Stones, Spiritual Accessory Crystals Macrame 14675 V 294 5.00 1
26681 999 Leather No-sew Square Crossbody Bag, Various Colors And Leather Material thanhsimone 14836 P 2257 0
2257 999 Leather No-sew Square Crossbody Bag, Various Colors And Leather Material thanhsimone 14836 P 0 0
26680 999 Leather No-sew Square Crossbody Bag, Various Colors And Leather Material thanhsimone 14836 P 2257 0
35732 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35855 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35856 998 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35857 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35858 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35859 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 P 0 4.91 11
35860 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35861 998 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35862 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35863 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35864 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35865 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35866 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35867 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35868 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35869 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35870 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35871 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35872 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35873 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35874 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35875 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35876 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35877 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
35878 999 Glazed Cat Ceramic Figurine With Flower Carvings HCERAMIC 15746 V 35859 4.91 11
46749 999 Leather Passport Holder 02, Handmade Design, Genuine Cowhide Material, Various Colors, Practical Gift, Various Styles Freewill Leather 16931 P 0 0
46750 999 Leather Passport Holder 02, Handmade Design, Genuine Cowhide Material, Various Colors, Practical Gift, Various Styles Freewill Leather 16931 V 46749 0
46840 999 Shine The Light Gift Set, Scented Candle Gift Box, Soy Wax Candle, Elegant Matchbox, Eco-Friendly Packaging, Relaxation Gifts, Self-Care Products, Aromatherapy Products, Gifts For Men And Women, Perfect Gift For Birthdays The Joy Box 16953 V 46715 4.95 20
46839 999 Shine The Light Gift Set, Scented Candle Gift Box, Soy Wax Candle, Elegant Matchbox, Eco-Friendly Packaging, Relaxation Gifts, Self-Care Products, Aromatherapy Products, Gifts For Men And Women, Perfect Gift For Birthdays The Joy Box 16953 V 46715 4.95 20
46838 999 Shine The Light Gift Set, Scented Candle Gift Box, Soy Wax Candle, Elegant Matchbox, Eco-Friendly Packaging, Relaxation Gifts, Self-Care Products, Aromatherapy Products, Gifts For Men And Women, Perfect Gift For Birthdays The Joy Box 16953 V 46715 4.95 20
46715 999 Shine The Light Gift Set, Scented Candle Gift Box, Soy Wax Candle, Elegant Matchbox, Eco-Friendly Packaging, Relaxation Gifts, Self-Care Products, Aromatherapy Products, Gifts For Men And Women, Perfect Gift For Birthdays The Joy Box 16953 P 0 4.95 20
47338 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47337 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47336 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47335 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47334 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47333 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47332 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47331 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47278 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 P 0 5.00 6
47340 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6
47339 999 Hand Embroidered Herbal Pillow, Vietnamese Herbal Pillow, Aromatherapy Pillow, Indulge In A Spa-Like Experience At Home, Promote Better Sleep, Perfect Gift For Women, Personalized Gift, Unique Gift Nhã An Hanoi 17031 V 47278 5.00 6