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, 
  1 as instock_marker, 
  IF(
    products.product_type = 'C', 
    (
      SELECT 
        MAX(amount) 
      FROM 
        cscart_products 
      WHERE 
        parent_product_id = products.product_id
    ) > 0, 
    IF(
      products.amount > 0, 
      1, 
      IF(products.tracking = 'O', 1, 0)
    )
  ) as instock_sorting 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'uk' 
  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 = 'uk' 
  LEFT JOIN cscart_images_links as img_links ON img_links.object_id = products.product_id 
  AND img_links.object_type = 'product' 
  AND img_links.type = 'M' 
WHERE 
  1 
  AND cscart_categories.category_id IN (
    387, 745, 741, 1112, 693, 679, 681, 659, 
    657, 1022, 1108, 582, 1073, 713, 574, 
    522, 470, 479, 481, 651, 515, 754, 760, 
    1097, 744, 1105, 1106, 726, 1110, 755, 
    743, 450, 1104, 429, 430, 434, 435, 448, 
    415, 1098, 594, 1066, 1107, 468, 489, 
    493, 497, 498, 499, 500, 758, 414, 388, 
    765, 768, 774, 777, 782, 784, 786, 1087, 
    792, 794, 1131, 819, 828, 1094, 833, 
    837, 844, 1134, 1109, 1099, 1100, 1101, 
    1102, 1103, 884, 885, 1088, 939, 941, 
    942, 950, 952, 961, 1186, 1021, 1016, 
    1017, 1028, 1032, 1135, 1188, 1168, 
    1185, 1156, 1157, 1162, 1166, 1169, 
    1171, 1179, 1181, 1182, 1183, 1184, 
    1192
  ) 
  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) 
  AND img_links.pair_id > 0 
  AND (
    1 
    AND products.product_id IN (
      6682, 
      6683, 
      6684, 
      6685, 
      6686, 
      6687, 
      6688, 
      6689, 
      6690, 
      6691, 
      6692, 
      6693, 
      6694, 
      6695, 
      6696, 
      6697, 
      6698, 
      6699, 
      6700, 
      6701, 
      6702, 
      6703, 
      6704, 
      6705, 
      6706, 
      6707, 
      6708, 
      6709, 
      6710, 
      6711, 
      6712, 
      6713, 
      6714, 
      6715, 
      6716, 
      6717, 
      6718, 
      6719, 
      6720, 
      6721, 
      6722, 
      6723, 
      6724, 
      6725, 
      6726, 
      6727, 
      6728, 
      6729, 
      6730, 
      6731, 
      6732, 
      6733, 
      6734, 
      6735, 
      6736, 
      6737, 
      6738, 
      6739, 
      6740, 
      6741, 
      6742, 
      6743, 
      6744, 
      6745, 
      6746, 
      6747, 
      6748, 
      6749, 
      6750, 
      6751, 
      6752, 
      6753, 
      6754, 
      6755, 
      6756, 
      6757, 
      6758, 
      6759, 
      6760, 
      6761, 
      6762, 
      6763, 
      6764, 
      6765, 
      6766, 
      6767, 
      6768, 
      6769, 
      6770, 
      6771, 
      6772, 
      6773, 
      6774, 
      6775, 
      6776, 
      6777, 
      6778, 
      6779, 
      6780, 
      6781, 
      6782, 
      6783, 
      6784, 
      6785, 
      6786, 
      6787, 
      6788, 
      6789, 
      6790, 
      6791, 
      6793, 
      6794, 
      6795, 
      6796, 
      6797, 
      6798, 
      6799, 
      6800, 
      6801, 
      6802, 
      6803, 
      6804, 
      6805, 
      6806, 
      6807, 
      6808, 
      6809, 
      6810, 
      6811, 
      6812, 
      6813, 
      6814, 
      6912, 
      6913, 
      6914, 
      6915, 
      6916, 
      6917, 
      6918, 
      6919, 
      6920, 
      6921, 
      6922, 
      6923, 
      6924, 
      7084, 
      7172, 
      7411, 
      7914, 
      8000, 
      8008, 
      8241, 
      8680, 
      8688, 
      8742, 
      8743, 
      8801, 
      8802, 
      8865, 
      9495, 
      10195, 
      10246, 
      10640, 
      10778, 
      10992, 
      11052, 
      11059, 
      11103, 
      11104, 
      11211, 
      11375, 
      11378, 
      20369, 
      20370, 
      20371, 
      21995, 
      21996, 
      22344, 
      12797, 
      12811, 
      20788, 
      12121
    )
  ) 
GROUP BY 
  product_id 
ORDER BY 
  instock_sorting DESC, 
  product asc, 
  products.product_id ASC 
LIMIT 
  72, 24

Query time 0.01511

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "if(products.product_type = 'C',(subquery#2) > 0,if(products.amount > 0,1,if(products.tracking = 'O',1,0))) desc, if(shared_descr.product_id is not null,shared_descr.product,descr1.product), products.product_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "system",
              "possible_keys": ["PRIMARY"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "read_sorted_file": {
              "filesort": {
                "sort_key": "case when products.parent_product_id <> 0 then products.parent_product_id else products.product_id end",
                "table": {
                  "table_name": "products",
                  "access_type": "range",
                  "possible_keys": ["PRIMARY", "status"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "rows": 182,
                  "filtered": 90.50141144,
                  "index_condition": "products.product_id in (6682,6683,6684,6685,6686,6687,6688,6689,6690,6691,6692,6693,6694,6695,6696,6697,6698,6699,6700,6701,6702,6703,6704,6705,6706,6707,6708,6709,6710,6711,6712,6713,6714,6715,6716,6717,6718,6719,6720,6721,6722,6723,6724,6725,6726,6727,6728,6729,6730,6731,6732,6733,6734,6735,6736,6737,6738,6739,6740,6741,6742,6743,6744,6745,6746,6747,6748,6749,6750,6751,6752,6753,6754,6755,6756,6757,6758,6759,6760,6761,6762,6763,6764,6765,6766,6767,6768,6769,6770,6771,6772,6773,6774,6775,6776,6777,6778,6779,6780,6781,6782,6783,6784,6785,6786,6787,6788,6789,6790,6791,6793,6794,6795,6796,6797,6798,6799,6800,6801,6802,6803,6804,6805,6806,6807,6808,6809,6810,6811,6812,6813,6814,6912,6913,6914,6915,6916,6917,6918,6919,6920,6921,6922,6923,6924,7084,7172,7411,7914,8000,8008,8241,8680,8688,8742,8743,8801,8802,8865,9495,10195,10246,10640,10778,10992,11052,11059,11103,11104,11211,11375,11378,20369,20370,20371,21995,21996,22344,12797,12811,20788,12121)",
                  "attached_condition": "products.company_id = 1 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": "descr1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "product_id"],
              "key": "PRIMARY",
              "key_length": "9",
              "used_key_parts": ["product_id", "lang_code"],
              "ref": ["lillyshop_lillyshop.products.product_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(descr1.lang_code = 'uk')"
            }
          },
          {
            "table": {
              "table_name": "shared_descr",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "product_id", "company_id"],
              "key": "PRIMARY",
              "key_length": "13",
              "used_key_parts": ["product_id", "lang_code", "company_id"],
              "ref": ["lillyshop_lillyshop.products.product_id", "const", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(shared_descr.lang_code = 'uk')"
            }
          },
          {
            "table": {
              "table_name": "prices",
              "access_type": "ref",
              "possible_keys": [
                "usergroup",
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "key": "usergroup",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["lillyshop_lillyshop.products.product_id"],
              "rows": 1,
              "filtered": 99.99145508,
              "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "pt"],
              "key": "pt",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["lillyshop_lillyshop.products.product_id"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["category_id"],
              "ref": ["lillyshop_lillyshop.products_categories.category_id"],
              "rows": 1,
              "filtered": 100,
              "index_condition": "cscart_categories.category_id in (387,745,741,1112,693,679,681,659,657,1022,1108,582,1073,713,574,522,470,479,481,651,515,754,760,1097,744,1105,1106,726,1110,755,743,450,1104,429,430,434,435,448,415,1098,594,1066,1107,468,489,493,497,498,499,500,758,414,388,765,768,774,777,782,784,786,1087,792,794,1131,819,828,1094,833,837,844,1134,1109,1099,1100,1101,1102,1103,884,885,1088,939,941,942,950,952,961,1186,1021,1016,1017,1028,1032,1135,1188,1168,1185,1156,1157,1162,1166,1169,1171,1179,1181,1182,1183,1184,1192)",
              "attached_condition": "(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)"
            }
          },
          {
            "table": {
              "table_name": "img_links",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "object_id"],
              "key": "object_id",
              "key_length": "81",
              "used_key_parts": ["object_id", "object_type", "type"],
              "ref": ["lillyshop_lillyshop.products.product_id", "const", "const"],
              "rows": 1,
              "filtered": 100,
              "index_condition": "img_links.object_id = products.product_id and img_links.object_type = 'product' and img_links.`type` = 'M'",
              "attached_condition": "img_links.pair_id > 0"
            }
          }
        ],
        "subqueries": [
          {
            "expression_cache": {
              "state": "uninitialized",
              "query_block": {
                "select_id": 2,
                "nested_loop": [
                  {
                    "table": {
                      "table_name": "cscart_products",
                      "access_type": "ALL",
                      "possible_keys": ["idx_parent_product_id"],
                      "rows": 11707,
                      "filtered": 100,
                      "attached_condition": "cscart_products.parent_product_id = products.product_id"
                    }
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_ids	product_types	product_type	instock_marker
6738	Гель-лак Kira Nails №067 темний фіолетовий емаль, 6 мл	LILLYSHOP	6738	P	P	1
6739	Гель-лак Kira Nails №068 холодна світла марсала емаль, 6 мл	LILLYSHOP	6739	P	P	1
6740	Гель-лак Kira Nails №069 винно-пурпурний емаль, 6 мл	LILLYSHOP	6740	P	P	1
6741	Гель-лак Kira Nails №070 коричнево-рожевий емаль, 6 мл	LILLYSHOP	6741	P	P	1
6742	Гель-лак Kira Nails №071 бежево-коричневий із золотим мікроблиском, 6 мл	LILLYSHOP	6742	P	P	1
6748	Гель-лак Kira Nails №072 помаранчевий емаль, 6 мл	LILLYSHOP	6748	P	P	1
8801	Гель-лак Kira Nails №073 сонячно-жовтий емаль, 6 мл	LILLYSHOP	8801	P	P	1
8802	Гель-лак Kira Nails №074 блідо-жовтий емаль, 6 мл	LILLYSHOP	8802	P	P	1
11103	Гель-лак Kira Nails №075 світло-жовтий емаль, 6 мл	LILLYSHOP	11103	P	P	1
6744	Гель-лак Kira Nails №076 ментоловий емаль, 6 мл	LILLYSHOP	6744	P	P	1
6745	Гель-лак Kira Nails №077 салатовий емаль, 6 мл	LILLYSHOP	6745	P	P	1
6746	Гель-лак Kira Nails №078 зелений емаль, 6 мл	LILLYSHOP	6746	P	P	1
6747	Гель-лак Kira Nails №079 насичений зелений емаль, 6 мл	LILLYSHOP	6747	P	P	1
10992	Гель-лак Kira Nails №080 темно-блакитний емаль, 6 мл	LILLYSHOP	10992	P	P	1
6749	Гель-лак Kira Nails №081 синій емаль, 6 мл	LILLYSHOP	6749	P	P	1
6750	Гель-лак Kira Nails №082 блакитний емаль, 6 мл	LILLYSHOP	6750	P	P	1
8743	Гель-лак Kira Nails №083 блідо-волошковий емаль, 6 мл	LILLYSHOP	8743	P	P	1
6751	Гель-лак Kira Nails №084 волошковий емаль, 6 мл	LILLYSHOP	6751	P	P	1
6752	Гель-лак Kira Nails №085 лілово-сірий емаль, 6 мл	LILLYSHOP	6752	P	P	1
6753	Гель-лак Kira Nails №086 зеленувато-бірюзовий емаль, 6 мл	LILLYSHOP	6753	P	P	1
6754	Гель-лак Kira Nails №087 світло-бузковий емаль, 6 мл	LILLYSHOP	6754	P	P	1
6755	Гель-лак Kira Nails №088 блідо-бузковий емаль, 6 мл	LILLYSHOP	6755	P	P	1
6756	Гель-лак Kira Nails №089 бузково-рожевий емаль, 6 мл	LILLYSHOP	6756	P	P	1
6757	Гель-лак Kira Nails №090 приглушений рожевий емаль, 6 мл	LILLYSHOP	6757	P	P	1