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 
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 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  AND cscart_categories.storefront_id IN (0, 1) 
  INNER JOIN cscart_product_variation_group_products AS variation_group_products ON variation_group_products.product_id = products.product_id 
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 (
    631, 745, 630, 629, 517, 226, 73, 50, 52, 
    54, 424, 47, 203, 975, 620, 102, 45, 431, 
    394, 213, 63, 623, 452, 204, 260, 87, 
    627
  ) 
  AND products.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  NULL asc

Query time 0.00946

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "198.23"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "ALL",
              "possible_keys": [
                "PRIMARY"
              ],
              "rows_examined_per_scan": 10,
              "rows_produced_per_join": 1,
              "filtered": "10.00",
              "cost_info": {
                "read_cost": "1.46",
                "eval_cost": "0.10",
                "prefix_cost": "1.56",
                "data_read_per_join": "6K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ],
              "attached_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`companies`.`status` = 'A')"
            }
          },
          {
            "table": {
              "table_name": "variation_group_products",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY",
                "idx_group_id"
              ],
              "key": "idx_group_id",
              "used_key_parts": [
                "group_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 218,
              "rows_produced_per_join": 218,
              "filtered": "100.00",
              "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`variation_group_products`.`group_id` in (631,745,630,629,517,226,73,50,52,54,424,47,203,975,620,102,45,431,394,213,63,623,452,204,260,87,627))",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "83.06",
                "eval_cost": "21.80",
                "prefix_cost": "106.42",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "product_id",
                "group_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "status"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "softwarepirmam_hewadelivard_cscart_4.variation_group_products.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 10,
              "filtered": "5.00",
              "cost_info": {
                "read_cost": "54.50",
                "eval_cost": "1.09",
                "prefix_cost": "182.72",
                "data_read_per_join": "48K"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "amount",
                "usergroup_ids",
                "tracking",
                "parent_product_id"
              ],
              "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`products`.`company_id` = `softwarepirmam_hewadelivard_cscart_4`.`companies`.`company_id`) and ((`softwarepirmam_hewadelivard_cscart_4`.`products`.`amount` > 0) or (`softwarepirmam_hewadelivard_cscart_4`.`products`.`tracking` = 'D')) and ((`softwarepirmam_hewadelivard_cscart_4`.`products`.`usergroup_ids` = '') or (0 <> find_in_set(0,`softwarepirmam_hewadelivard_cscart_4`.`products`.`usergroup_ids`)) or (0 <> find_in_set(1,`softwarepirmam_hewadelivard_cscart_4`.`products`.`usergroup_ids`))) and (`softwarepirmam_hewadelivard_cscart_4`.`products`.`status` = 'A') and (`softwarepirmam_hewadelivard_cscart_4`.`products`.`product_type` <> 'D'))"
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "pt",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "softwarepirmam_hewadelivard_cscart_4.variation_group_products.product_id"
              ],
              "rows_examined_per_scan": 2,
              "rows_produced_per_join": 21,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "5.45",
                "eval_cost": "2.18",
                "prefix_cost": "190.35",
                "data_read_per_join": "348"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "softwarepirmam_hewadelivard_cscart_4.products_categories.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "5.00",
              "cost_info": {
                "read_cost": "5.45",
                "eval_cost": "0.11",
                "prefix_cost": "197.98",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "category_id",
                "storefront_id",
                "usergroup_ids",
                "status"
              ],
              "attached_condition": "(((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`))) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`status` in ('A','H')) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`storefront_id` in (0,1)))"
            }
          },
          {
            "table": {
              "table_name": "descr1",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "product_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "softwarepirmam_hewadelivard_cscart_4.variation_group_products.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.14",
                "eval_cost": "0.11",
                "prefix_cost": "198.23",
                "data_read_per_join": "4K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product"
              ]
            }
          }
        ]
      }
    }
  }
}

Result

product_id amount product company_name variation_group_id product_type parent_product_id
908 100 Xiaomi Buds 5 PRO Delivard Store 45 P 0
134 100 Xiaomi Buds 5 PRO Delivard Store 45 V 908
919 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
918 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
924 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
922 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
921 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
925 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
525 500 Apple iPhone 16 Pro Max Delivard Store 47 P 0
917 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
916 500 Apple iPhone 16 Pro Max Delivard Store 47 V 525
955 500 Apple iPhone 16 Pro Delivard Store 50 V 944
942 500 Apple iPhone 16 Pro Delivard Store 50 V 944
941 500 Apple iPhone 16 Pro Delivard Store 50 V 944
446 500 Apple iPhone 16 Pro Delivard Store 50 V 944
953 500 Apple iPhone 16 Pro Delivard Store 50 V 944
952 500 Apple iPhone 16 Pro Delivard Store 50 V 944
951 500 Apple iPhone 16 Pro Delivard Store 50 V 944
949 500 Apple iPhone 16 Pro Delivard Store 50 V 944
948 500 Apple iPhone 16 Pro Delivard Store 50 V 944
947 500 Apple iPhone 16 Pro Delivard Store 50 V 944
946 500 Apple iPhone 16 Pro Delivard Store 50 V 944
945 500 Apple iPhone 16 Pro Delivard Store 50 V 944
944 500 Apple iPhone 16 Pro Delivard Store 50 P 0
982 100 Apple iPhone 16 Delivard Store 52 V 478
980 100 Apple iPhone 16 Delivard Store 52 V 478
979 100 Apple iPhone 16 Delivard Store 52 V 478
978 100 Apple iPhone 16 Delivard Store 52 V 478
981 100 Apple iPhone 16 Delivard Store 52 V 478
478 100 Apple iPhone 16 Delivard Store 52 P 0
977 100 Apple iPhone 16 Delivard Store 52 V 478
975 100 Apple iPhone 16 Delivard Store 52 V 478
974 100 Apple iPhone 16 Delivard Store 52 V 478
973 100 Apple iPhone 16 Delivard Store 52 V 478
972 100 Apple iPhone 16 Delivard Store 52 V 478
971 100 Apple iPhone 16 Delivard Store 52 V 478
970 100 Apple iPhone 16 Delivard Store 52 V 478
994 100 Apple iPhone 16 Plus Delivard Store 54 V 985
996 100 Apple iPhone 16 Plus Delivard Store 54 V 985
493 100 Apple iPhone 16 Plus Delivard Store 54 V 985
985 100 Apple iPhone 16 Plus Delivard Store 54 P 0
986 100 Apple iPhone 16 Plus Delivard Store 54 V 985
988 100 Apple iPhone 16 Plus Delivard Store 54 V 985
990 100 Apple iPhone 16 Plus Delivard Store 54 V 985
991 100 Apple iPhone 16 Plus Delivard Store 54 V 985
993 100 Apple iPhone 16 Plus Delivard Store 54 V 985
1139 500 Apple Watch Series 10 Delivard Store 63 V 1134
1138 500 Apple Watch Series 10 Delivard Store 63 V 1134
1137 500 Apple Watch Series 10 Delivard Store 63 V 1134
1136 500 Apple Watch Series 10 Delivard Store 63 V 1134
1135 500 Apple Watch Series 10 Delivard Store 63 V 1134
1134 500 Apple Watch Series 10 Delivard Store 63 P 0
1133 500 Apple Watch Series 10 Delivard Store 63 V 1134
1132 500 Apple Watch Series 10 Delivard Store 63 V 1134
1131 500 Apple Watch Series 10 Delivard Store 63 V 1134
1130 500 Apple Watch Series 10 Delivard Store 63 V 1134
1129 500 Apple Watch Series 10 Delivard Store 63 V 1134
827 500 Apple Watch Series 10 Delivard Store 63 V 1134
1254 100 Xiaomi Smart Band 9 PRO Delivard Store 73 V 247
1253 100 Xiaomi Smart Band 9 PRO Delivard Store 73 V 247
247 100 Xiaomi Smart Band 9 PRO Delivard Store 73 P 0
1406 100 Samsung Galaxy S24 FE Delivard Store 87 P 0
1407 100 Samsung Galaxy S24 FE Delivard Store 87 V 1406
1408 100 Samsung Galaxy S24 FE Delivard Store 87 V 1406
1409 100 Samsung Galaxy S24 FE Delivard Store 87 V 1406
1410 100 Samsung Galaxy S24 FE Delivard Store 87 V 1406
1411 100 Samsung Galaxy S24 FE Delivard Store 87 V 1406
1412 100 Samsung Galaxy S24 FE Delivard Store 87 V 1406
1274 500 Samsung Galaxy Tab A9+ Delivard Store 102 P 0
1483 500 Samsung Galaxy Tab A9+ Delivard Store 102 V 1274
1484 500 Samsung Galaxy Tab A9+ Delivard Store 102 V 1274
1485 500 Samsung Galaxy Tab A9+ Delivard Store 102 V 1274
1486 500 Samsung Galaxy Tab A9+ Delivard Store 102 V 1274
1487 500 Samsung Galaxy Tab A9+ Delivard Store 102 V 1274
2478 100 Xiaomi 14T Pro Delivard Store 203 P 0
2483 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2482 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2484 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2485 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2486 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2487 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2488 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2489 100 Xiaomi 14T Pro Delivard Store 203 V 2478
2490 100 Xiaomi 14T Delivard Store 204 P 0
2491 100 Xiaomi 14T Delivard Store 204 V 2490
2492 100 Xiaomi 14T Delivard Store 204 V 2490
2493 100 Xiaomi 14T Delivard Store 204 V 2490
2494 100 Xiaomi 14T Delivard Store 204 V 2490
2495 100 Xiaomi 14T Delivard Store 204 V 2490
2496 100 Xiaomi 14T Delivard Store 204 V 2490
2497 100 Xiaomi 14T Delivard Store 204 V 2490
2524 100 Xiaomi Redmi A5 Delivard Store 213 V 2527
2525 100 Xiaomi Redmi A5 Delivard Store 213 V 2527
2526 100 Xiaomi Redmi A5 Delivard Store 213 V 2527
2527 100 Xiaomi Redmi A5 Delivard Store 213 P 0
2528 100 Xiaomi Redmi A5 Delivard Store 213 V 2527
2529 100 Xiaomi Redmi A5 Delivard Store 213 V 2527
2530 100 Xiaomi Redmi A5 Delivard Store 213 V 2527
2531 100 Xiaomi Redmi A5 Delivard Store 213 V 2527
8422 100 Xiaomi Redmi Pad Pro 5G Delivard Store 226 V 2574
2574 100 Xiaomi Redmi Pad Pro 5G Delivard Store 226 P 0
2698 100 Anker 544 USB-C to USB-C Cable Bio-Based 3ft Delivard Store 260 P 0
3452 99 Pensan TRIBALL Ballpoint Pen Delivard Store 394 P 0
3454 100 Pensan TRIBALL Ballpoint Pen Delivard Store 394 V 3452
3455 100 Pensan TRIBALL Ballpoint Pen Delivard Store 394 V 3452
3456 100 Pensan TRIBALL Ballpoint Pen Delivard Store 394 V 3452
3754 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 P 0
3755 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3756 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3757 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3758 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3759 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3760 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3761 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3762 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3764 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
3765 100 Apple MacBook Air (13-inch, M4) Delivard Store 424 V 3754
1930 100 Apple Mac Book Pro 14-inch, M4 Delivard Store 431 P 0
3815 100 Apple Mac Book Pro 14-inch, M4 Delivard Store 431 V 1930
3817 100 Apple Mac Book Pro 14-inch, M4 Delivard Store 431 V 1930
3818 100 Apple Mac Book Pro 14-inch, M4 Delivard Store 431 V 1930
4022 100 Apple Mac mini (M4 or M4 Pro) Delivard Store 452 P 0
4021 100 Apple Mac mini (M4 or M4 Pro) Delivard Store 452 V 4022
4020 100 Apple Mac mini (M4 or M4 Pro) Delivard Store 452 V 4022
4432 100 iPad (A16) Delivard Store 517 P 0
4438 100 iPad (A16) Delivard Store 517 V 4432
4440 100 iPad (A16) Delivard Store 517 V 4432
4441 100 iPad (A16) Delivard Store 517 V 4432
4442 100 iPad (A16) Delivard Store 517 V 4432
4443 100 iPad (A16) Delivard Store 517 V 4432
4444 100 iPad (A16) Delivard Store 517 V 4432
4448 100 iPad (A16) Delivard Store 517 V 4432
4445 100 iPad (A16) Delivard Store 517 V 4432
4446 99 iPad (A16) Delivard Store 517 V 4432
4447 100 iPad (A16) Delivard Store 517 V 4432
6149 100 Apple 49mm Trail Loop (2024) Delivard Store 620 V 6151
5685 100 Apple 49mm Trail Loop (2024) Delivard Store 620 V 6151
6148 100 Apple 49mm Trail Loop (2024) Delivard Store 620 V 6151
6150 100 Apple 49mm Trail Loop (2024) Delivard Store 620 V 6151
6151 100 Apple 49mm Trail Loop (2024) Delivard Store 620 P 0
5696 100 Apple Smart Folio for iPad mini (A17 Pro) Delivard Store 623 P 0
5697 100 Apple Smart Folio for iPad mini (A17 Pro) Delivard Store 623 V 5696
5698 100 Apple Smart Folio for iPad mini (A17 Pro) Delivard Store 623 V 5696
5699 100 Apple Smart Folio for iPad mini (A17 Pro) Delivard Store 623 V 5696
5721 100 Apple 46mm Milanese Loop Delivard Store 627 P 0
5722 100 Apple 46mm Milanese Loop Delivard Store 627 V 5721
5723 100 Apple 46mm Milanese Loop Delivard Store 627 V 5721
5727 100 Apple iPhone 17 Pro Delivard Store 629 P 0
5729 99 Apple iPhone 17 Pro Delivard Store 629 V 5727
5730 100 Apple iPhone 17 Pro Delivard Store 629 V 5727
5731 100 Apple iPhone 17 Pro Delivard Store 629 V 5727
5732 99 Apple iPhone 17 Pro Delivard Store 629 V 5727
5733 100 Apple iPhone 17 Pro Delivard Store 629 V 5727
5734 100 Apple iPhone 17 Pro Delivard Store 629 V 5727
5735 100 Apple iPhone 17 Pro Delivard Store 629 V 5727
5736 100 Apple iPhone 17 Pro Delivard Store 629 V 5727
5739 100 Apple iPhone 17 Delivard Store 630 P 0
5741 100 Apple iPhone 17 Delivard Store 630 V 5739
5742 100 Apple iPhone 17 Delivard Store 630 V 5739
5743 100 Apple iPhone 17 Delivard Store 630 V 5739
5744 100 Apple iPhone 17 Delivard Store 630 V 5739
5745 100 Apple iPhone 17 Delivard Store 630 V 5739
5746 100 Apple iPhone 17 Delivard Store 630 V 5739
5747 100 Apple iPhone 17 Delivard Store 630 V 5739
5748 100 Apple iPhone 17 Delivard Store 630 V 5739
5749 100 Apple iPhone 17 Delivard Store 630 V 5739
5750 100 Apple iPhone Air Delivard Store 631 P 0
5752 100 Apple iPhone Air Delivard Store 631 V 5750
5753 100 Apple iPhone Air Delivard Store 631 V 5750
5754 100 Apple iPhone Air Delivard Store 631 V 5750
5755 100 Apple iPhone Air Delivard Store 631 V 5750
5756 100 Apple iPhone Air Delivard Store 631 V 5750
5757 100 Apple iPhone Air Delivard Store 631 V 5750
5758 100 Apple iPhone Air Delivard Store 631 V 5750
5759 100 Apple iPhone Air Delivard Store 631 V 5750
5760 100 Apple iPhone Air Delivard Store 631 V 5750
5761 100 Apple iPhone Air Delivard Store 631 V 5750
5762 100 Apple iPhone Air Delivard Store 631 V 5750
7004 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7005 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7006 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7007 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7008 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7009 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7010 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7011 99 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7012 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7013 100 Apple iPhone 17 Pro Max Delivard Store 745 P 0
7014 98 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
7015 100 Apple iPhone 17 Pro Max Delivard Store 745 V 7013
10284 100 Apple iPhone 15 Delivard Store 975 P 0
10285 100 Apple iPhone 15 Delivard Store 975 V 10284
10286 100 Apple iPhone 15 Delivard Store 975 V 10284
10287 100 Apple iPhone 15 Delivard Store 975 V 10284
10288 100 Apple iPhone 15 Delivard Store 975 V 10284
10289 100 Apple iPhone 15 Delivard Store 975 V 10284
10290 100 Apple iPhone 15 Delivard Store 975 V 10284
10291 100 Apple iPhone 15 Delivard Store 975 V 10284
10292 100 Apple iPhone 15 Delivard Store 975 V 10284
10293 100 Apple iPhone 15 Delivard Store 975 V 10284