SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  companies.ec_rfq_whatsapp_number, 
  companies.ec_rfq_bht, 
  companies.ec_rfq_phone_number, 
  companies.ec_rfq_bhf, 
  products.product_type, 
  products.parent_product_id, 
  cscart_ec_table_booking_system.booking_type as booking_type 
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_ec_table_booking_system ON cscart_ec_table_booking_system.product_id = products.product_id 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
WHERE 
  1 
  AND cscart_categories.category_id IN (
    6328, 
    10398, 
    10518, 
    10519, 
    10520, 
    10521, 
    10542, 
    10543, 
    10544, 
    10545, 
    10629, 
    10437, 
    10438, 
    10439, 
    10440, 
    10441, 
    10442, 
    10443, 
    10444, 
    10446, 
    10447, 
    10445, 
    6340, 
    6379, 
    6425, 
    6544, 
    6581, 
    6615, 
    6664, 
    6703, 
    6761, 
    6795, 
    6827, 
    6847, 
    6967, 
    7003, 
    7059, 
    7090, 
    7119, 
    7150, 
    7182, 
    7237, 
    7268, 
    7287, 
    7355, 
    7395, 
    7470, 
    7476, 
    7540, 
    7541, 
    7543, 
    7545, 
    7548, 
    7551, 
    7554, 
    7558, 
    7575, 
    7581, 
    9033, 
    9035, 
    7668, 
    7678, 
    7683, 
    7713, 
    7717, 
    7721, 
    7728, 
    7735, 
    7742, 
    7747, 
    7768, 
    7754, 
    7783, 
    7798, 
    7802, 
    7831, 
    7917, 
    7948, 
    7955, 
    7965, 
    8074, 
    8076, 
    8099, 
    8129, 
    8175, 
    8178, 
    8184, 
    8190, 
    8198, 
    8130, 
    8136, 
    6471, 
    6504, 
    6538, 
    6563, 
    6594, 
    6628, 
    6663, 
    6689, 
    6731, 
    6851, 
    6884, 
    6914, 
    9037, 
    9038, 
    9040, 
    9041, 
    9042, 
    9043, 
    9044, 
    9045, 
    9046, 
    9047, 
    9048, 
    9050, 
    9051, 
    9052, 
    9053, 
    9054, 
    9055, 
    9057, 
    9058, 
    9059, 
    9060, 
    9061, 
    9062, 
    9063, 
    9064, 
    9065, 
    6483, 
    6500, 
    6525, 
    6545, 
    6560, 
    6591, 
    6622, 
    6665, 
    6704, 
    6767, 
    6798, 
    6860, 
    6907, 
    6960, 
    6997, 
    7021, 
    7075, 
    7106, 
    7133, 
    7174, 
    7198, 
    7220, 
    7276, 
    7292, 
    7313, 
    7335, 
    7367, 
    7388, 
    7455, 
    7460, 
    7466, 
    7474, 
    7479, 
    7556, 
    7562, 
    7570, 
    7714, 
    7720, 
    7730, 
    7800, 
    7816, 
    7836, 
    8143, 
    7850, 
    6964, 
    7009, 
    7060, 
    7102, 
    7142, 
    7187, 
    7279, 
    7312, 
    7358, 
    7393, 
    7458, 
    7467, 
    7542, 
    7547, 
    7552, 
    7565, 
    7574, 
    7586, 
    7597, 
    7610, 
    7619, 
    7632, 
    7637, 
    7646, 
    7656, 
    7247, 
    7264, 
    7282, 
    7294, 
    7310, 
    7322, 
    7341, 
    7360, 
    7376, 
    7389, 
    7399, 
    7463, 
    7468, 
    7473, 
    7477, 
    7481, 
    9066, 
    9067, 
    9068, 
    9069, 
    9070, 
    9071, 
    9072, 
    9073, 
    7620, 
    9078, 
    9079, 
    9080, 
    9081, 
    7633, 
    7644, 
    9082, 
    9083, 
    9074, 
    9075, 
    9076, 
    9077, 
    9031, 
    10396, 
    9084, 
    9085, 
    9086, 
    9087, 
    9088, 
    9089, 
    9090, 
    9091, 
    9092, 
    9093, 
    9094, 
    9095, 
    9096, 
    9097, 
    9098
  ) 
  AND products.product_id NOT IN (286517) 
  AND companies.status IN ('A') 
  AND products.company_id IN (
    1, 
    25, 
    34, 
    35, 
    36, 
    37, 
    38, 
    39, 
    40, 
    41, 
    42, 
    43, 
    44, 
    45, 
    46, 
    47, 
    48, 
    49, 
    50, 
    51, 
    52, 
    53, 
    54, 
    55, 
    56, 
    57, 
    58, 
    59, 
    60, 
    61, 
    62, 
    63, 
    64, 
    65, 
    66, 
    67, 
    68, 
    69, 
    70, 
    71, 
    72, 
    73, 
    74, 
    75, 
    76, 
    77, 
    78, 
    79, 
    81, 
    82, 
    83, 
    84, 
    85, 
    86, 
    87, 
    88, 
    89, 
    90, 
    91, 
    92, 
    93, 
    94, 
    95, 
    96, 
    97, 
    98, 
    99, 
    100, 
    101, 
    102, 
    103, 
    104, 
    105, 
    106, 
    108, 
    109, 
    110, 
    111, 
    122, 
    123, 
    124, 
    125, 
    126, 
    127, 
    128, 
    129, 
    130, 
    131, 
    132, 
    134, 
    135, 
    136, 
    137, 
    138, 
    139, 
    140, 
    141, 
    142, 
    143, 
    144, 
    145, 
    146, 
    148, 
    150, 
    152, 
    153, 
    154, 
    155, 
    156, 
    157, 
    158, 
    159, 
    160, 
    161, 
    162, 
    163, 
    164, 
    165, 
    166, 
    167, 
    168, 
    169, 
    170, 
    171, 
    172, 
    173, 
    174, 
    175, 
    176, 
    177, 
    178, 
    179, 
    180, 
    181, 
    182, 
    183, 
    184, 
    185, 
    186, 
    187, 
    188, 
    189, 
    190, 
    191, 
    192, 
    193, 
    194, 
    196, 
    197, 
    198, 
    199, 
    200, 
    202, 
    203, 
    204, 
    205, 
    206, 
    207, 
    208, 
    210, 
    211, 
    212, 
    213, 
    214, 
    215, 
    216, 
    217, 
    218, 
    219, 
    220, 
    221, 
    222, 
    223, 
    224, 
    225, 
    226, 
    227, 
    228, 
    229, 
    230, 
    231, 
    232, 
    233, 
    234, 
    235, 
    236, 
    237, 
    238, 
    239, 
    240, 
    241, 
    242, 
    243, 
    244, 
    245, 
    246, 
    247, 
    248, 
    249, 
    250, 
    251, 
    252, 
    253, 
    254, 
    255, 
    256, 
    257, 
    259, 
    260, 
    261, 
    262, 
    263, 
    264, 
    266, 
    268, 
    269, 
    271, 
    272, 
    273, 
    276, 
    277, 
    278, 
    279, 
    280, 
    281, 
    282, 
    284, 
    285, 
    286, 
    287, 
    288, 
    289, 
    290, 
    291, 
    292, 
    293, 
    294, 
    295, 
    296, 
    297, 
    298, 
    299, 
    300, 
    301, 
    302, 
    303, 
    304, 
    305, 
    306, 
    307, 
    308, 
    309, 
    310, 
    311, 
    312, 
    313, 
    314, 
    315, 
    316, 
    317, 
    318, 
    319, 
    321, 
    322, 
    323, 
    324, 
    325, 
    326, 
    327, 
    328, 
    329, 
    330, 
    331, 
    332, 
    333, 
    334, 
    335, 
    336, 
    337, 
    338, 
    339, 
    340, 
    341, 
    342, 
    343, 
    344, 
    345, 
    346, 
    347, 
    348, 
    349, 
    350, 
    351, 
    352, 
    353, 
    354, 
    355, 
    356, 
    357, 
    358, 
    359, 
    360, 
    361, 
    362, 
    363, 
    364, 
    365, 
    366, 
    367, 
    368, 
    369, 
    370, 
    371, 
    372, 
    373, 
    374, 
    377, 
    378, 
    379, 
    380, 
    381, 
    382, 
    383, 
    384, 
    385, 
    387, 
    388, 
    389, 
    391, 
    392, 
    393, 
    394, 
    395, 
    396, 
    397, 
    398, 
    400, 
    401, 
    402, 
    403, 
    404, 
    405, 
    406, 
    407, 
    408, 
    409, 
    410, 
    411, 
    414, 
    415, 
    416, 
    417, 
    418, 
    419, 
    420, 
    421, 
    422, 
    423, 
    424, 
    425, 
    426, 
    427, 
    428, 
    429, 
    430, 
    431, 
    432, 
    433, 
    434, 
    435, 
    436, 
    439, 
    440, 
    443, 
    445, 
    447, 
    448, 
    449, 
    450, 
    451, 
    452, 
    454, 
    455, 
    457, 
    458, 
    459, 
    460, 
    461, 
    462, 
    463, 
    465, 
    466, 
    467, 
    468, 
    469, 
    470, 
    471, 
    472, 
    473, 
    474, 
    476, 
    478, 
    479, 
    480, 
    481, 
    482, 
    483, 
    484, 
    485, 
    486, 
    487, 
    488, 
    489, 
    491, 
    492, 
    493, 
    494, 
    495, 
    496, 
    497, 
    498, 
    501, 
    503, 
    504, 
    505, 
    507, 
    508, 
    509, 
    510, 
    511, 
    512, 
    513, 
    514, 
    515, 
    516, 
    518, 
    519, 
    520, 
    522, 
    524, 
    525, 
    526, 
    527, 
    528, 
    531, 
    532, 
    533, 
    535, 
    537, 
    539, 
    540, 
    541, 
    542, 
    543, 
    545, 
    546, 
    547, 
    548, 
    549, 
    550, 
    551, 
    553, 
    554, 
    556, 
    557, 
    558, 
    559, 
    560, 
    561, 
    562, 
    563, 
    564, 
    566, 
    567, 
    568, 
    569, 
    575, 
    576, 
    577, 
    579, 
    580, 
    582, 
    583, 
    585, 
    586, 
    587, 
    588, 
    590, 
    591, 
    592, 
    593, 
    594, 
    595, 
    596, 
    597, 
    598, 
    600, 
    602, 
    603, 
    604, 
    606, 
    607, 
    609, 
    610, 
    611, 
    613, 
    618, 
    620, 
    621, 
    622, 
    623, 
    624, 
    625, 
    626, 
    627, 
    628, 
    629, 
    630, 
    631, 
    632, 
    633, 
    634, 
    635, 
    636, 
    637, 
    639, 
    641, 
    642, 
    643, 
    644, 
    646, 
    647, 
    649, 
    655, 
    660, 
    661, 
    662, 
    663, 
    665, 
    668, 
    669, 
    670, 
    671, 
    672, 
    673, 
    674, 
    675, 
    676, 
    678, 
    679, 
    680, 
    681, 
    682, 
    685, 
    686, 
    687, 
    688, 
    689, 
    690, 
    691, 
    692, 
    693, 
    694, 
    695, 
    696, 
    697, 
    707, 
    719, 
    0
  ) 
  AND products.company_id = 592 
  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 prices.usergroup_id IN (0, 0, 1) 
  AND products.company_id = 592 
  AND products.parent_product_id = 0 
GROUP BY 
  products.product_id 
ORDER BY 
  popularity.total desc, 
  products.product_id ASC 
LIMIT 
  0, 10

Query time 0.01779

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2508.69"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.20",
                "prefix_cost": "0.00",
                "data_read_per_join": "9K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company",
                "ec_rfq_whatsapp_number",
                "ec_rfq_phone_number",
                "ec_rfq_bhf",
                "ec_rfq_bht"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 252,
              "rows_produced_per_join": 10,
              "filtered": "4.00",
              "cost_info": {
                "read_cost": "351.90",
                "eval_cost": "2.02",
                "prefix_cost": "353.91",
                "data_read_per_join": "59K"
              },
              "used_columns": [
                "category_id",
                "usergroup_ids",
                "status",
                "storefront_id"
              ],
              "attached_condition": "((`webmarco`.`cscart_categories`.`category_id` in (6328,10398,10518,10519,10520,10521,10542,10543,10544,10545,10629,10437,10438,10439,10440,10441,10442,10443,10444,10446,10447,10445,6340,6379,6425,6544,6581,6615,6664,6703,6761,6795,6827,6847,6967,7003,7059,7090,7119,7150,7182,7237,7268,7287,7355,7395,7470,7476,7540,7541,7543,7545,7548,7551,7554,7558,7575,7581,9033,9035,7668,7678,7683,7713,7717,7721,7728,7735,7742,7747,7768,7754,7783,7798,7802,7831,7917,7948,7955,7965,8074,8076,8099,8129,8175,8178,8184,8190,8198,8130,8136,6471,6504,6538,6563,6594,6628,6663,6689,6731,6851,6884,6914,9037,9038,9040,9041,9042,9043,9044,9045,9046,9047,9048,9050,9051,9052,9053,9054,9055,9057,9058,9059,9060,9061,9062,9063,9064,9065,6483,6500,6525,6545,6560,6591,6622,6665,6704,6767,6798,6860,6907,6960,6997,7021,7075,7106,7133,7174,7198,7220,7276,7292,7313,7335,7367,7388,7455,7460,7466,7474,7479,7556,7562,7570,7714,7720,7730,7800,7816,7836,8143,7850,6964,7009,7060,7102,7142,7187,7279,7312,7358,7393,7458,7467,7542,7547,7552,7565,7574,7586,7597,7610,7619,7632,7637,7646,7656,7247,7264,7282,7294,7310,7322,7341,7360,7376,7389,7399,7463,7468,7473,7477,7481,9066,9067,9068,9069,9070,9071,9072,9073,7620,9078,9079,9080,9081,7633,7644,9082,9083,9074,9075,9076,9077,9031,10396,9084,9085,9086,9087,9088,9089,9090,9091,9092,9093,9094,9095,9096,9097,9098)) and ((`webmarco`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`webmarco`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`webmarco`.`cscart_categories`.`usergroup_ids`)) and (`webmarco`.`cscart_categories`.`status` in ('A','H')) and (`webmarco`.`cscart_categories`.`storefront_id` in (0,1)))"
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.cscart_categories.category_id"
              ],
              "rows_examined_per_scan": 179,
              "rows_produced_per_join": 1342,
              "filtered": "74.22",
              "using_index": true,
              "cost_info": {
                "read_cost": "12.71",
                "eval_cost": "268.52",
                "prefix_cost": "728.41",
                "data_read_per_join": "20K"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ],
              "attached_condition": "(`webmarco`.`products_categories`.`product_id` <> 286517)"
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "age_verification",
                "status",
                "idx_parent_product_id",
                "idx_master_product_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 67,
              "filtered": "5.00",
              "cost_info": {
                "read_cost": "1342.59",
                "eval_cost": "13.43",
                "prefix_cost": "2339.53",
                "data_read_per_join": "524K"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "amount",
                "usergroup_ids",
                "tracking",
                "parent_product_id"
              ],
              "attached_condition": "((`webmarco`.`products`.`parent_product_id` = 0) and (`webmarco`.`products`.`company_id` = 592) and ((`webmarco`.`products`.`amount` > 0) or (`webmarco`.`products`.`tracking` = 'D')) and ((`webmarco`.`products`.`usergroup_ids` = '') or find_in_set(0,`webmarco`.`products`.`usergroup_ids`) or find_in_set(1,`webmarco`.`products`.`usergroup_ids`)) and (`webmarco`.`products`.`status` = 'A'))"
            }
          },
          {
            "table": {
              "table_name": "cscart_ec_table_booking_system",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "4",
              "ref": [
                "webmarco.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 67,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "67.13",
                "eval_cost": "13.43",
                "prefix_cost": "2420.08",
                "data_read_per_join": "11K"
              },
              "used_columns": [
                "product_id",
                "booking_type"
              ],
              "attached_condition": "<if>(is_not_null_compl(cscart_ec_table_booking_system), (`webmarco`.`cscart_ec_table_booking_system`.`product_id` = `webmarco`.`products_categories`.`product_id`), true)"
            }
          },
          {
            "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": [
                "webmarco.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3,
              "filtered": "5.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "67.13",
                "eval_cost": "0.67",
                "prefix_cost": "2500.64",
                "data_read_per_join": "214"
              },
              "used_columns": [
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "attached_condition": "((`webmarco`.`prices`.`lower_limit` = 1) and (`webmarco`.`prices`.`usergroup_id` in (0,0,1)))"
            }
          },
          {
            "table": {
              "table_name": "popularity",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "total"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "3.36",
                "eval_cost": "0.67",
                "prefix_cost": "2504.67",
                "data_read_per_join": "107"
              },
              "used_columns": [
                "product_id",
                "total"
              ]
            }
          },
          {
            "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": [
                "webmarco.products_categories.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "3.36",
                "eval_cost": "0.67",
                "prefix_cost": "2508.69",
                "data_read_per_join": "22K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product"
              ]
            }
          }
        ]
      }
    }
  }
}