Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS (
    CASE WHEN products.parent_product_id <> 0 THEN products.parent_product_id ELSE products.product_id END
  ) AS product_id, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.product, descr1.product
  ) as product, 
  companies.company as company_name, 
  GROUP_CONCAT(
    products.product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_ids, 
  GROUP_CONCAT(
    products.product_type 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_types, 
  GROUP_CONCAT(
    products.parent_product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS parent_product_ids, 
  products.product_type, 
  products.parent_product_id 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_features_values as var_val_18 ON var_val_18.product_id = products.product_id 
  AND var_val_18.lang_code = 'en' 
  AND var_val_18.feature_id = 18 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'en' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  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) 
  LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id 
  AND shared_descr.company_id = 1 
  AND shared_descr.lang_code = 'en' 
WHERE 
  1 
  AND (
    var_val_18.variant_id IN (1202)
  ) 
  AND cscart_categories.category_id IN (308) 
  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 prices.usergroup_id IN (0, 0, 1) 
GROUP BY 
  product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  768, 24

Query time 0.05481

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1583.57"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "buffer_result": {
          "using_temporary_table": true,
          "nested_loop": [
            {
              "table": {
                "table_name": "cscart_categories",
                "access_type": "const",
                "possible_keys": [
                  "PRIMARY",
                  "c_status",
                  "p_category_id"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "category_id"
                ],
                "key_length": "3",
                "ref": [
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 1,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "0.00",
                  "eval_cost": "0.10",
                  "prefix_cost": "0.00",
                  "data_read_per_join": "4K"
                },
                "used_columns": [
                  "category_id",
                  "storefront_id",
                  "usergroup_ids",
                  "status"
                ]
              }
            },
            {
              "table": {
                "table_name": "companies",
                "access_type": "ALL",
                "possible_keys": [
                  "PRIMARY"
                ],
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 1,
                "filtered": "50.00",
                "cost_info": {
                  "read_cost": "0.62",
                  "eval_cost": "0.10",
                  "prefix_cost": "0.72",
                  "data_read_per_join": "5K"
                },
                "used_columns": [
                  "company_id",
                  "status",
                  "company"
                ],
                "attached_condition": "(`gaseus`.`companies`.`status` = 'A')"
              }
            },
            {
              "table": {
                "table_name": "products_categories",
                "access_type": "ref",
                "possible_keys": [
                  "PRIMARY",
                  "pt"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "category_id"
                ],
                "key_length": "3",
                "ref": [
                  "const"
                ],
                "rows_examined_per_scan": 2529,
                "rows_produced_per_join": 2529,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "16.05",
                  "eval_cost": "252.90",
                  "prefix_cost": "269.67",
                  "data_read_per_join": "39K"
                },
                "used_columns": [
                  "product_id",
                  "category_id"
                ]
              }
            },
            {
              "table": {
                "table_name": "products",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "status"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "gaseus.products_categories.product_id"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 252,
                "filtered": "10.00",
                "cost_info": {
                  "read_cost": "632.25",
                  "eval_cost": "25.28",
                  "prefix_cost": "1154.82",
                  "data_read_per_join": "1M"
                },
                "used_columns": [
                  "product_id",
                  "product_type",
                  "status",
                  "company_id",
                  "usergroup_ids",
                  "parent_product_id"
                ],
                "attached_condition": "((`gaseus`.`products`.`company_id` = `gaseus`.`companies`.`company_id`) and ((`gaseus`.`products`.`usergroup_ids` = '') or (0 <> find_in_set(0,`gaseus`.`products`.`usergroup_ids`)) or (0 <> find_in_set(1,`gaseus`.`products`.`usergroup_ids`))) and (`gaseus`.`products`.`status` = 'A'))"
              }
            },
            {
              "table": {
                "table_name": "prices",
                "access_type": "ref",
                "possible_keys": [
                  "usergroup",
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "key": "usergroup",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "gaseus.products_categories.product_id"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 252,
                "filtered": "99.97",
                "using_index": true,
                "cost_info": {
                  "read_cost": "63.20",
                  "eval_cost": "25.27",
                  "prefix_cost": "1243.30",
                  "data_read_per_join": "5K"
                },
                "used_columns": [
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "attached_condition": "((`gaseus`.`prices`.`lower_limit` = 1) and (`gaseus`.`prices`.`usergroup_id` in (0,0,1)))"
              }
            },
            {
              "table": {
                "table_name": "var_val_18",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "fl",
                  "variant_id",
                  "lang_code",
                  "product_id",
                  "fpl",
                  "idx_product_feature_variant_id"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "feature_id",
                  "product_id",
                  "variant_id",
                  "lang_code"
                ],
                "key_length": "15",
                "ref": [
                  "const",
                  "gaseus.products_categories.product_id",
                  "const",
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 252,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "63.18",
                  "eval_cost": "25.27",
                  "prefix_cost": "1331.76",
                  "data_read_per_join": "195K"
                },
                "used_columns": [
                  "feature_id",
                  "product_id",
                  "variant_id",
                  "lang_code"
                ]
              }
            },
            {
              "table": {
                "table_name": "shared_descr",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "product_id",
                  "company_id"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id",
                  "lang_code",
                  "company_id"
                ],
                "key_length": "13",
                "ref": [
                  "gaseus.products_categories.product_id",
                  "const",
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 252,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "74.38",
                  "eval_cost": "25.27",
                  "prefix_cost": "1431.41",
                  "data_read_per_join": "963K"
                },
                "used_columns": [
                  "product_id",
                  "lang_code",
                  "company_id",
                  "product"
                ]
              }
            },
            {
              "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": [
                  "gaseus.products_categories.product_id",
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 252,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "126.89",
                  "eval_cost": "25.27",
                  "prefix_cost": "1583.57",
                  "data_read_per_join": "1M"
                },
                "used_columns": [
                  "product_id",
                  "lang_code",
                  "product"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Result

product_id	product	company_name	product_ids	product_types	parent_product_ids	product_type	parent_product_id
6641	Main regulator Oxygen 0-10 bar Vulkan H10/300 Effect	Gaseus	6641,456,457,458,459,460,461,462,463,464,2296,2297,2298,2299,2300,2301,2302,2303,2304,2359,2360,2361,2362,2363,2364,2365,2366,2367,2457,2458,2459,2460,2461,2462,2463,2464,2465,2468,2469,2470,2471,2472,2473,2474,2475,2476,2538,2539,2540,2541,2542,2543,2544,2545,2546,2627,2628,2629,2630,2631,2632,2633,2634,2635,2787,2788,2789,2790,2791,2883,2884,2885,2886,2887,2888,2889,2890,2891,2892,2893,2894,2895,2896,2897,2898,2899,2900,2901,2902,2903,2904,2905,2906,2907,2908,2909,2960,2961,2962,2963,2964,2965,2966,2967,2968,3055,3056,3057,3058,3059,3060,3061,3062,3063,3511,3512,3513,3514,3515,3516,3517,3518,3626,3851,3852,4262,4263,4264,4265,4266,4267,4268,4269,5895,5896,5897,5898,5899,5900,5901,5902,5903,5959,5960,5961,5962,5963,5964,5965,5966,5967,6057,6058,6059,6060,6061,6062,6063,6064,6065,6068,6069,6070,6071,6072,6073,6074,6075,6076,6130,6131,6132,6133,6134,6135,6136,6137,6138,6220,6221,6222,6223,6224,6225,6226,6227,6228,6375,6376,6377,6468,6469,6470,6471,6472,6473,6474,6475,6476,6477,6478,6479,6480,6481,6482,6483,6484,6485,6486,6487,6488,6489,6490,6491,6492,6493,6494,6544,6545,6546,6547,6548,6549,6550,6551,6552,6633,6634,6635,6636,6637,6638,6639,6640,7530,7531,7532,7533,7534,7535,7536,7537,7538,7539,7540,7541,7542,7543,7544,7545,7546,7547,7548,7549,7550,7551,7552,7553,7554,7555,7556,7557,7558,7559,7560,7561,7562,7563,7564,7565,7566,7567,7568,7569,7570,7571,7572,7573,7574,7575,7576,7577,7578,7579,7580,7581,7582,7583,7586,7587,7588,7589,7590,7591,7592,7593,7594,7595,7596,7597,7598,7599,7600,7601,7602,7603,7604,7605,7606,7607,7608,7609,7610,7611,7612,7613,7614,7615,7616,7617,7618,7619,7620,7621,7624,7625,7626,7627,7628,7629,7630,7631,7632,7633,7634,7635,7636,7637,7638,7639,7640,7641,7642,7643,7644,7645,7646,7647,7648,7649,7650,7651,7652,7653,7654,7655,7656,7657,7658,7659,7662,7663,7664,7665,7666,7667,7668,7669,7670,7671,7672,7673,7674,7675,7676,7677,7678,7679,7680,7681,7682,7683,7684,7685,7686,7687,7688,7689,7690,7691,7692,7693,7694,7695,7696,7697	P,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V	0,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641,6641	V	6641
6219	Main regulator Oxygen 0-10 bar Vulkan H10/300 Effect for SE10	Gaseus	6219,2295,2358,2466,2467,2537,2626,5894,5958,6066,6067,6129,7492,7493	P,V,V,V,V,V,V,V,V,V,V,V,V,V	0,6219,6219,6219,6219,6219,6219,6219,6219,6219,6219,6219,6219,6219	V	6219
2715	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	2715	P	0	P	0
2716	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	2716	P	0	P	0
2717	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	2717	P	0	P	0
2718	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	2718	P	0	P	0
2719	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	2719	P	0	P	0
6303	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	6303	P	0	P	0
6304	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	6304	P	0	P	0
6305	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	6305	P	0	P	0
6306	Main regulator Oxygen 0-60 bar Vulkan H60/200 Effect	Gaseus	6306	P	0	P	0
5819	Main regulator Vulkan H20/300 Effect Compressed air left side for AM 35H-20bar	Gaseus	5819,1517,1518,1519,1520,1521,1522,1523,1524,1525,1537,1538,1539,1540,1541,1542,1543,1544,1545,2217,2218,2219,2735,2863,2864,3729,3732,5220,5221,5222,5223,5224,5225,5226,5227,5237,5238,5239,5240,5241,5242,5243,5244,5817,5818,6322,6448,6449,7494,7495,7498,7499,7504,7505,7526,7527	P,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V	0,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819,5819	V	5819
3722	Main regulator Vulkan H20/300 Effect Compressed air, 0-20 bar for Vareo EB/BS 60	Gaseus	3722,1434,1435,1436,1437,1438,1439,3721,5145,5146,5147,5148,7502,7503,7584,7585,7622,7623,7660,7661,7698,7699	P,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V,V	0,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722,3722	V	3722
1500	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	1500	P	0	P	0
1501	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	1501	P	0	P	0
1502	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	1502	P	0	P	0
1503	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	1503	P	0	P	0
3726	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	3726	P	0	P	0
5206	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	5206	P	0	P	0
5207	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	5207	P	0	P	0
5208	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	5208	P	0	P	0
5209	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	5209	P	0	P	0
6736	Main regulator Vulkan H40/200 Effect Inert gas left side for AM 40-25bar	Gaseus	6736	P	0	P	0
1490	Main regulator Vulkan H40/200 Effect Inert gas right side for AM 40-25bar	Gaseus	1490	P	0	P	0