SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  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_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_warehouses_sum_products_amount as war_sum_amount ON war_sum_amount.product_id = products.product_id 
  LEFT JOIN cscart_warehouses_destination_products_amount AS warehouses_destination_products_amount ON warehouses_destination_products_amount.product_id = products.product_id 
  AND warehouses_destination_products_amount.destination_id = 0 
  AND warehouses_destination_products_amount.storefront_id = 0 
WHERE 
  1 
  AND cscart_categories.category_id IN (
    20163, 
    20164, 
    20165, 
    20166, 
    20167, 
    20168, 
    20169, 
    20170, 
    20171, 
    20172, 
    20173, 
    20174, 
    20175, 
    20176, 
    20177, 
    20178, 
    20179, 
    20180, 
    20181, 
    20182, 
    20183, 
    20184, 
    20185, 
    20187, 
    20188, 
    20189, 
    20190, 
    20191, 
    20192, 
    20193, 
    20194, 
    20195, 
    20196, 
    20197, 
    20198, 
    20199, 
    20200, 
    20201, 
    20202, 
    20203, 
    20204, 
    20205, 
    20206, 
    20207, 
    20208, 
    20209, 
    20210, 
    20211, 
    20212, 
    20213, 
    20214, 
    20215, 
    20216, 
    20217, 
    20218, 
    20219, 
    20220, 
    20221, 
    20222, 
    20223, 
    20224, 
    20225, 
    20226, 
    20227, 
    20228, 
    20229, 
    20230, 
    20231, 
    20232, 
    20233, 
    20234, 
    20235, 
    20236, 
    20237, 
    20238, 
    20239, 
    20240, 
    20241, 
    20242, 
    20243, 
    20244, 
    20245, 
    20246, 
    20247, 
    20248, 
    20249, 
    20250, 
    20251, 
    20252, 
    20253, 
    20254, 
    20255, 
    20256, 
    20257, 
    20258, 
    20259, 
    20260, 
    20261, 
    20262, 
    20263, 
    20264, 
    20265, 
    20266, 
    20267, 
    20268, 
    20269, 
    20270, 
    20271, 
    20272, 
    20273, 
    20274, 
    20275, 
    20276, 
    20277, 
    20278, 
    20279, 
    20280, 
    20281, 
    20282, 
    20283, 
    20284, 
    20285, 
    20286, 
    20287, 
    20288, 
    20289, 
    20290, 
    20291, 
    20292, 
    20293, 
    20294, 
    20295, 
    20296, 
    20297, 
    20298, 
    20299, 
    20300, 
    20301, 
    20302, 
    20303, 
    20304, 
    20305, 
    20306, 
    20307, 
    20308, 
    20309, 
    20310, 
    20311, 
    20312, 
    20313, 
    20314, 
    20315, 
    20316, 
    20317, 
    20318, 
    20319, 
    20320, 
    20321, 
    20322, 
    20323, 
    20324, 
    20325, 
    20326, 
    20327, 
    20328, 
    20329, 
    20330, 
    20331, 
    20332, 
    20333, 
    20334, 
    20335, 
    20336, 
    20337, 
    20338, 
    20339, 
    20340, 
    20341, 
    20342, 
    20343, 
    20344, 
    20345, 
    20347, 
    20348, 
    20349, 
    20350, 
    20351, 
    20352, 
    20353, 
    20354, 
    20355, 
    20356, 
    20357, 
    20358, 
    20359, 
    20360, 
    20361, 
    20362, 
    20363, 
    20364, 
    20365, 
    20366, 
    20367, 
    20368, 
    20369, 
    20370, 
    20371, 
    20372, 
    20373, 
    20374, 
    20375, 
    20376, 
    20377, 
    20378, 
    20379, 
    20380, 
    20381, 
    20382, 
    20383, 
    20384, 
    20385, 
    20386, 
    20387, 
    20388, 
    20389, 
    20390, 
    20391, 
    20392, 
    20393, 
    20394, 
    20395, 
    20396, 
    20397, 
    20398, 
    20399, 
    20400, 
    20401, 
    20402, 
    20403, 
    20404, 
    20405, 
    20406, 
    20407, 
    20408, 
    20409, 
    20410, 
    20411, 
    20412, 
    20413, 
    20414, 
    20415, 
    20416, 
    20417, 
    20418, 
    20419, 
    20420, 
    20421, 
    20422, 
    20423, 
    20424, 
    20425, 
    20426, 
    20427, 
    20428, 
    20429, 
    20430, 
    20431, 
    20432, 
    20433, 
    20434, 
    20435, 
    20436, 
    20437, 
    20438, 
    20439, 
    20440, 
    20441, 
    20442, 
    20443, 
    20444, 
    20445, 
    20446, 
    20447, 
    20448, 
    20449, 
    20450, 
    20451, 
    20452, 
    20453, 
    20454, 
    20455, 
    20456, 
    20457, 
    20458, 
    20459, 
    20460, 
    20461, 
    20462, 
    20463, 
    20464, 
    20465, 
    20466, 
    20467, 
    20468, 
    20469, 
    20470, 
    20471, 
    20472, 
    20473, 
    20474, 
    20475, 
    20476, 
    20477, 
    20478, 
    20479, 
    20480, 
    20481, 
    20482, 
    20483, 
    20484, 
    20485, 
    20486, 
    20487, 
    20488, 
    20489, 
    20490, 
    20491, 
    20492, 
    20493, 
    20494, 
    20495, 
    20496, 
    20497, 
    20498, 
    20499, 
    20500, 
    20501, 
    20502, 
    20503, 
    20504, 
    20505, 
    20506, 
    20507, 
    20508, 
    20509, 
    20510, 
    20511, 
    20512, 
    20513, 
    20514, 
    20515, 
    20516, 
    20517, 
    20518, 
    20519, 
    20520, 
    20521, 
    20522, 
    20523, 
    20524, 
    20525, 
    20526, 
    20527, 
    20528, 
    20529, 
    20530, 
    20531, 
    20532, 
    20533, 
    20534, 
    20535, 
    20536, 
    20537, 
    20538, 
    20539, 
    20540, 
    20541, 
    20542, 
    20543, 
    20544, 
    20545, 
    20546, 
    20547, 
    20548, 
    20549, 
    20550, 
    20551, 
    20552, 
    20553, 
    20554, 
    20555, 
    20556, 
    20557, 
    20558, 
    20559, 
    20560, 
    20561, 
    20562, 
    20563, 
    20564, 
    20565, 
    20566, 
    20567, 
    20568, 
    20569, 
    20570, 
    20571, 
    20572, 
    20573, 
    20574, 
    20575, 
    20576, 
    20577, 
    20578, 
    20579, 
    20580, 
    20581, 
    20582, 
    20583, 
    20584, 
    20585, 
    20586, 
    20587, 
    20588, 
    20589, 
    20590, 
    20591, 
    20592, 
    20593, 
    20594, 
    20595, 
    20596, 
    20597, 
    20598, 
    20599, 
    20600, 
    20601, 
    20602, 
    20603, 
    20604, 
    20605, 
    20606, 
    20607, 
    20608, 
    20609, 
    20610, 
    20611, 
    20612, 
    20613, 
    20614, 
    20615, 
    20616, 
    20617, 
    20618, 
    20619, 
    20620, 
    20621, 
    20622, 
    20623, 
    20624, 
    20625, 
    20626, 
    20627, 
    20628, 
    20629, 
    20630, 
    20631, 
    20632, 
    20633, 
    20634, 
    20635, 
    20636, 
    20637, 
    20638, 
    20639, 
    20640, 
    20641, 
    20642, 
    20643, 
    20644, 
    20645, 
    20646, 
    20647, 
    20648, 
    20649, 
    20650, 
    20651, 
    20652, 
    20653, 
    20654, 
    20655, 
    20656, 
    20657, 
    20658, 
    20659, 
    20660, 
    20661, 
    20662, 
    20663, 
    20664, 
    20665, 
    20666, 
    20667, 
    20668, 
    20669, 
    20670, 
    20671, 
    20672, 
    20673, 
    20674, 
    20675, 
    20676, 
    20677, 
    20678, 
    20679, 
    20680, 
    20681, 
    20682, 
    20683, 
    20684, 
    20685, 
    20686, 
    20687, 
    20688, 
    20689, 
    20690, 
    20691, 
    20692, 
    20693, 
    20694, 
    20695, 
    20696, 
    20697, 
    20698, 
    20699, 
    20700, 
    20701, 
    20702, 
    20703, 
    20704, 
    20705, 
    20706, 
    20707, 
    20708, 
    20709, 
    20710, 
    20711, 
    20712, 
    20713, 
    20714, 
    20715, 
    20716, 
    20717, 
    20718, 
    20719, 
    20720, 
    20721, 
    20722, 
    20723, 
    20724, 
    20725, 
    20726, 
    20727, 
    20728, 
    20729, 
    20730, 
    20731, 
    20732, 
    20733, 
    20734, 
    20735, 
    20736, 
    20737, 
    20738, 
    20739, 
    20740, 
    20741, 
    20742, 
    20743, 
    20744, 
    20745, 
    20746, 
    20747, 
    20748, 
    20749, 
    20750, 
    20751, 
    20752, 
    20753, 
    20754, 
    20755, 
    20756, 
    20757, 
    20758, 
    20759, 
    20760, 
    20761, 
    20762, 
    20763, 
    20764, 
    20765, 
    20766, 
    20767, 
    20768, 
    20769, 
    20770, 
    20771, 
    20772, 
    20773, 
    20774, 
    20775, 
    20776, 
    20777, 
    20778, 
    20779, 
    20780, 
    20781, 
    20782, 
    20783, 
    20784, 
    20785, 
    20786, 
    20787, 
    20788, 
    20789, 
    20790, 
    20791, 
    20792, 
    20793, 
    20794, 
    20795, 
    20796, 
    20797, 
    20798, 
    20799, 
    20800, 
    20801, 
    20802, 
    20803, 
    20804, 
    20805, 
    20806, 
    20807, 
    20808, 
    20809, 
    20810, 
    20811, 
    20812, 
    20813, 
    20814, 
    20815, 
    20816, 
    20817, 
    20818, 
    20819, 
    20820, 
    20821, 
    20822, 
    20823, 
    20824, 
    20825, 
    20826, 
    20827, 
    20828, 
    20829, 
    20830, 
    20831, 
    20832, 
    20833, 
    20834, 
    20835, 
    20836, 
    20837, 
    20838, 
    20839, 
    20840, 
    20841, 
    20842, 
    20843, 
    20844, 
    20845, 
    20846, 
    20847, 
    20848, 
    20849, 
    20850, 
    20851, 
    20852, 
    20853, 
    20854, 
    20855, 
    20856, 
    20857, 
    20858, 
    20859, 
    20860, 
    20861, 
    20862, 
    20863, 
    20864, 
    20865, 
    20866, 
    20867, 
    20868, 
    20869, 
    20870, 
    20871, 
    20872, 
    20873, 
    20874, 
    20875, 
    20876, 
    20877, 
    20878, 
    20879, 
    20880, 
    20881, 
    20882, 
    20883, 
    20884, 
    20885, 
    20886, 
    20887, 
    20888, 
    20889, 
    20890, 
    20891, 
    20892, 
    20893, 
    20894, 
    20895, 
    20896, 
    20897, 
    20898, 
    20899, 
    20900, 
    20901, 
    20902, 
    20903, 
    20904, 
    20905, 
    20906, 
    20907, 
    20908, 
    20909, 
    20910, 
    20911, 
    20912, 
    20913, 
    20914, 
    20915, 
    20916, 
    20917, 
    20918, 
    20919, 
    20920, 
    20921, 
    20922, 
    20923, 
    20924, 
    20925, 
    20926, 
    20927, 
    20928, 
    20929, 
    20930, 
    20931, 
    20932, 
    20933, 
    20934, 
    20935, 
    20936, 
    20937, 
    20938, 
    20939, 
    20940, 
    20941, 
    20942, 
    20943, 
    20944, 
    20945, 
    20946, 
    20947, 
    20948, 
    20949, 
    20950, 
    20951, 
    20952, 
    20953, 
    20954, 
    20955, 
    20956, 
    20957, 
    20958, 
    20959, 
    20960, 
    20961, 
    20962, 
    20963, 
    20964, 
    20965, 
    20966, 
    20967, 
    20968, 
    20969, 
    20970, 
    20971, 
    20972, 
    20973, 
    20974, 
    20975, 
    20976, 
    20977, 
    20978, 
    20979, 
    20980, 
    20981, 
    20982, 
    20983, 
    20984, 
    20985, 
    20986, 
    20987, 
    20988, 
    20989, 
    20990, 
    20991, 
    20992, 
    20993, 
    20994, 
    20995, 
    20996, 
    20997, 
    20998, 
    20999, 
    21000, 
    21001, 
    21002, 
    21003, 
    21004, 
    21005, 
    21006, 
    21007, 
    21008, 
    21009, 
    21010, 
    21011, 
    21012, 
    21013, 
    21014, 
    21015, 
    21016, 
    21017, 
    21018, 
    21019, 
    21020, 
    21021, 
    21022, 
    21023, 
    21024, 
    21025, 
    21026, 
    21027, 
    21028, 
    21029, 
    21030, 
    21031, 
    21032, 
    21033, 
    21034, 
    21035, 
    21036, 
    21037, 
    21038, 
    21039, 
    21040, 
    21041, 
    21042, 
    21043, 
    21044, 
    21045, 
    21046, 
    21047, 
    21048, 
    21049, 
    21050, 
    21051, 
    21052, 
    21053, 
    21054, 
    21055, 
    21056, 
    21057, 
    21058, 
    21059, 
    21060, 
    21061, 
    21062, 
    21063, 
    21064, 
    21065, 
    21066, 
    21067, 
    21068, 
    21069, 
    21070, 
    21071, 
    21072, 
    21073, 
    21074, 
    21075, 
    21076, 
    21077, 
    21078, 
    21079, 
    21080, 
    21081, 
    21082, 
    21083, 
    21084, 
    21085, 
    21086, 
    21087, 
    21088, 
    21089, 
    21090, 
    21092, 
    21093, 
    21094, 
    21095, 
    21096, 
    21097, 
    21098, 
    21099, 
    21100, 
    21101, 
    21102, 
    21103, 
    21104, 
    21105, 
    21106, 
    21108, 
    21109, 
    21110, 
    21111, 
    21112, 
    21113, 
    21114, 
    21115, 
    21116, 
    21117, 
    21118, 
    21119, 
    21120, 
    21121, 
    21122, 
    21123, 
    21124, 
    21125, 
    21126, 
    21127, 
    21128, 
    21129, 
    21130, 
    21131, 
    21132, 
    21133, 
    21134, 
    21135, 
    21136, 
    21137, 
    21138, 
    21139, 
    21140, 
    21141, 
    21142, 
    21143, 
    21144, 
    21145, 
    21146, 
    21147, 
    21148, 
    21149, 
    21150, 
    21151, 
    21152, 
    21153, 
    21154, 
    21155, 
    21156, 
    21157, 
    21158, 
    21159, 
    21160, 
    21161, 
    21162, 
    21163, 
    21164, 
    21165, 
    21166, 
    21167, 
    21168, 
    21169, 
    21170, 
    21171, 
    21172, 
    21173, 
    21174, 
    21175, 
    21176, 
    21177, 
    21178, 
    21179, 
    21180, 
    21181, 
    21182, 
    21183, 
    21184, 
    21185, 
    21186, 
    21187, 
    21188, 
    21189, 
    21190, 
    21191, 
    21192, 
    21193, 
    21194, 
    21195, 
    21196, 
    21197, 
    23211, 
    21107, 
    21091, 
    20186, 
    23212
  ) 
  AND companies.status IN ('A') 
  AND products.company_id IN (2, 64, 92, 103, 104, 121, 127, 0) 
  AND (
    CASE products.is_stock_split_by_warehouses WHEN 'Y' THEN warehouses_destination_products_amount.amount ELSE products.amount END
  ) > 0 
  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 products.parent_product_id = 0 
  AND products.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  products.timestamp desc, 
  products.product_id ASC 
LIMIT 
  0, 64

Query time 0.00008

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "products.`timestamp` desc, products.product_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "war_sum_amount",
              "access_type": "system",
              "possible_keys": ["PRIMARY"],
              "rows": 0,
              "filtered": 0,
              "const_row_not_found": true
            }
          },
          {
            "table": {
              "table_name": "warehouses_destination_products_amount",
              "access_type": "system",
              "possible_keys": ["PRIMARY", "idx_storefront_id"],
              "rows": 0,
              "filtered": 0,
              "const_row_not_found": true
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "ALL",
              "possible_keys": ["PRIMARY", "status", "idx_parent_product_id"],
              "rows": 67,
              "filtered": 79.83960724,
              "attached_condition": "products.parent_product_id = 0 and products.company_id in (2,64,92,103,104,121,127,0) and case products.is_stock_split_by_warehouses when 'Y' then NULL else products.amount end > 0 and (products.usergroup_ids = '' or find_in_set(0,products.usergroup_ids) or find_in_set(1,products.usergroup_ids)) and products.`status` = 'A' and products.product_type <> 'D'"
            }
          },
          {
            "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": ["ebonza_cscart_test.products.product_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(descr1.lang_code = 'en')"
            }
          },
          {
            "table": {
              "table_name": "companies",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["company_id"],
              "ref": ["ebonza_cscart_test.products.company_id"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "companies.`status` = 'A'"
            }
          },
          {
            "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": ["ebonza_cscart_test.products.product_id"],
              "rows": 1,
              "filtered": 90.5405426,
              "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": ["ebonza_cscart_test.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": ["ebonza_cscart_test.products_categories.category_id"],
              "rows": 1,
              "filtered": 100,
              "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)"
            }
          },
          {
            "duplicates_removal": [
              {
                "table": {
                  "table_name": "<derived3>",
                  "access_type": "ref",
                  "possible_keys": ["key0"],
                  "key": "key0",
                  "key_length": "4",
                  "used_key_parts": ["_col_1"],
                  "ref": ["ebonza_cscart_test.products_categories.category_id"],
                  "rows": 10,
                  "filtered": 100,
                  "attached_condition": "products_categories.category_id = tvc_0._col_1",
                  "materialized": {
                    "query_block": {
                      "union_result": {
                        "query_specifications": [
                          {
                            "query_block": {
                              "select_id": 3,
                              "table": {
                                "message": "No tables used"
                              }
                            }
                          }
                        ]
                      }
                    }
                  }
                }
              }
            ]
          }
        ]
      }
    }
  }
}

Result

product_id product company_name product_type parent_product_id
227240 Cage RetediCZ P 0
227223 Soap RENMAR P 0