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 (
    16986, 16973, 16988, 16930, 13748, 13954, 
    12788, 15673, 12785, 12784, 12791, 
    12792, 14216, 12783
  ) 
GROUP BY 
  products.product_id 
ORDER BY 
  NULL asc

Query time 0.02631

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": 70,
            "filtered": 100,
            "index_condition": "variation_group_products.group_id in (16986,16973,16988,16930,13748,13954,12788,15673,12785,12784,12791,12792,14216,12783)"
          }
        },
        {
          "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
18400 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 V 4271 5.00 2
18399 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 V 4271 5.00 2
18398 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 V 4271 5.00 2
18397 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 V 4271 5.00 2
18396 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 V 4271 5.00 2
18395 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 V 4271 5.00 2
18394 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 V 4271 5.00 2
4271 999 Spectator Derby Brogue Leather Shoes, Olive & Gray, Genuine Leather Shoes, Brogue Shoes For Men, Non-Slip Rubber Sole Shoes Daddy's Shoes 12783 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
18435 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 V 4266 5.00 1
18434 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 V 4266 5.00 1
18433 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 V 4266 5.00 1
18432 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 V 4266 5.00 1
18431 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 V 4266 5.00 1
18430 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 V 4266 5.00 1
18429 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 V 4266 5.00 1
4266 999 White-Cream Derby Brogue Leather Shoes, Office Shoes For Men, Handmade Genuine Leather Shoes, Low-Collar Men's Shoes, Derby Shoes For Men Daddy's Shoes 12788 P 0 5.00 1
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
18463 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 V 4262 5.00 3
18462 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 V 4262 5.00 3
18461 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 V 4262 5.00 3
18460 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 V 4262 5.00 3
18459 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 V 4262 5.00 3
18458 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 V 4262 5.00 3
18457 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 V 4262 5.00 3
4262 999 Leather Denim Boots, Denim Patched Leather Boots, Rugged Style Shoes, Genuine Cowhide Leather Boots, Workwear Fashion Daddy's Shoes 12792 P 0 5.00 3
22405 95 Deluxe Agarwood Pen Engraved Name Included Wooden Pen Case, Long-lasting Sandalwood Material, Exquisite Craftsmanship, Premium Gift Tamy Beloved 13748 V 1892 5.00 13
1892 94 Deluxe Agarwood Pen Engraved Name Included Wooden Pen Case, Long-lasting Sandalwood Material, Exquisite Craftsmanship, Premium Gift Tamy Beloved 13748 P 0 5.00 13
1481 1997 Leather Minimalist Wallet Tamy Beloved 13954 V 23162 4.71 7
23162 1997 Leather Minimalist Wallet Tamy Beloved 13954 P 0 4.71 7
23163 1997 Leather Minimalist Wallet Tamy Beloved 13954 V 23162 4.71 7
24120 999 2 Layered Genuine Leather Passport Cover, Genuine Leather, Genuine Leather Accessories, Wallet with Name Card, Handmade Gift, Personalized Gift Tamy Beloved 14216 V 1056 5.00 11
24119 998 2 Layered Genuine Leather Passport Cover, Genuine Leather, Genuine Leather Accessories, Wallet with Name Card, Handmade Gift, Personalized Gift Tamy Beloved 14216 V 1056 5.00 11
24118 999 2 Layered Genuine Leather Passport Cover, Genuine Leather, Genuine Leather Accessories, Wallet with Name Card, Handmade Gift, Personalized Gift Tamy Beloved 14216 V 1056 5.00 11
1056 999 2 Layered Genuine Leather Passport Cover, Genuine Leather, Genuine Leather Accessories, Wallet with Name Card, Handmade Gift, Personalized Gift Tamy Beloved 14216 P 0 5.00 11
35195 999 Leather Strap, Versatile Strap, Made Of Durable Cowhide Leather, Unique And Distinctive Bag Accessory, Vintage Color Chu Du Bag 15673 P 0 5.00 1
35258 999 Leather Strap, Versatile Strap, Made Of Durable Cowhide Leather, Unique And Distinctive Bag Accessory, Vintage Color Chu Du Bag 15673 V 35195 5.00 1
35259 999 Leather Strap, Versatile Strap, Made Of Durable Cowhide Leather, Unique And Distinctive Bag Accessory, Vintage Color Chu Du Bag 15673 V 35195 5.00 1
46748 999 Genuine Leather Passport Holder 01, Peace Of Mind And Style For Your Travels, Compact And Convenient Design, Unique And Personalized Gift Freewill Leather 16930 V 49306 5.00 9
49306 999 Genuine Leather Passport Holder 01, Peace Of Mind And Style For Your Travels, Compact And Convenient Design, Unique And Personalized Gift Freewill Leather 16930 P 0 5.00 9
49305 999 Genuine Leather Passport Holder 01, Peace Of Mind And Style For Your Travels, Compact And Convenient Design, Unique And Personalized Gift Freewill Leather 16930 V 49306 5.00 9
46993 998 Secret Message Scented Candle with Lid, Essential Oil Scented Candles, Handmade Scented Candles, Wooden Wicks, Glass Jars, Personalized Gifts Nico studio 16973 P 0 5.00 6
47014 997 "The Kiss" Hand-painted Leather Ring, Silver Color, Leather Ring Arts Collection, Gustav Klimt Inspired Accessory, Unique Gift, Stylish Gift ĐỎ 16986 P 0 5.00 17
47106 999 "The Kiss" Hand-painted Leather Ring, Silver Color, Leather Ring Arts Collection, Gustav Klimt Inspired Accessory, Unique Gift, Stylish Gift ĐỎ 16986 V 47014 5.00 17
47107 999 "The Kiss" Hand-painted Leather Ring, Silver Color, Leather Ring Arts Collection, Gustav Klimt Inspired Accessory, Unique Gift, Stylish Gift ĐỎ 16986 V 47014 5.00 17
47016 999 "The Scream" Ring, Silver Color, Leather Ring Arts Collection, Hand-Painted Leather Ring, Edvard Munch Pattern, Gift Idea For Art Lovers ĐỎ 16988 P 0 5.00 5
47110 999 "The Scream" Ring, Silver Color, Leather Ring Arts Collection, Hand-Painted Leather Ring, Edvard Munch Pattern, Gift Idea For Art Lovers ĐỎ 16988 V 47016 5.00 5
47111 998 "The Scream" Ring, Silver Color, Leather Ring Arts Collection, Hand-Painted Leather Ring, Edvard Munch Pattern, Gift Idea For Art Lovers ĐỎ 16988 V 47016 5.00 5