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, 
  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, 
  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, 
  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 (
    5372, 
    5495, 
    5506, 
    5511, 
    5519, 
    5525, 
    5612, 
    5639, 
    5647, 
    5652, 
    5661, 
    8981, 
    8972, 
    8973, 
    8974, 
    8975, 
    8976, 
    8977, 
    8978, 
    8979, 
    8980, 
    5672, 
    8957, 
    8958, 
    8959, 
    8960, 
    8961, 
    8962, 
    8963, 
    8964, 
    8965, 
    8966, 
    8967, 
    8968, 
    8969, 
    8970, 
    8971, 
    5577, 
    5584, 
    5599, 
    5608, 
    5619, 
    5623, 
    5632, 
    5640, 
    5649, 
    5655, 
    5662, 
    5669, 
    5675, 
    5686, 
    5694, 
    5706, 
    5712, 
    5719, 
    5726, 
    5734, 
    5743, 
    5752, 
    5763, 
    5771, 
    5778, 
    5796, 
    5809, 
    5817, 
    5828, 
    5840, 
    5851, 
    5863, 
    5871, 
    5878, 
    5891, 
    5904, 
    5917, 
    5925, 
    5933, 
    6113, 
    6141, 
    6153, 
    6104, 
    6123, 
    6142, 
    6173, 
    6215, 
    6235, 
    6271, 
    6278, 
    6289, 
    6302, 
    6380, 
    6396, 
    6407, 
    6421, 
    6437, 
    6450, 
    6461, 
    6469, 
    6479, 
    6492, 
    6509, 
    6515, 
    6520, 
    6537, 
    6548, 
    6558, 
    6569, 
    6585, 
    6606, 
    6624, 
    6645, 
    6672, 
    6687, 
    6702, 
    6721, 
    6736, 
    6750, 
    6771, 
    6793, 
    6811, 
    6822, 
    6839, 
    6853, 
    6866, 
    6874, 
    6888, 
    6949, 
    6969, 
    6993, 
    7010, 
    7046, 
    7065, 
    7084, 
    7101, 
    7121, 
    7147, 
    8949, 
    8950, 
    8951, 
    8952, 
    8784, 
    8790, 
    8791, 
    8793, 
    8794, 
    8795, 
    8796, 
    8801, 
    8806, 
    8824, 
    8826, 
    8827, 
    8828, 
    8829, 
    8830, 
    8797, 
    8799, 
    8802, 
    8804, 
    8807, 
    8808, 
    8809, 
    8811, 
    8813, 
    8798, 
    8800, 
    8803, 
    8805, 
    8810, 
    8812, 
    8814, 
    8815, 
    8816, 
    8819, 
    8821, 
    8817, 
    8818, 
    8820, 
    8822, 
    8823, 
    8825, 
    8831, 
    8832, 
    8833, 
    8834, 
    8835, 
    8836, 
    8837, 
    8838, 
    8839, 
    8840, 
    8841, 
    8842, 
    8843, 
    8844, 
    8845, 
    8846, 
    8847, 
    8848, 
    8849, 
    8850, 
    8953, 
    8954, 
    8955, 
    8956, 
    8945, 
    8946, 
    8947, 
    8948, 
    5630, 
    5860, 
    5869, 
    5656, 
    5881, 
    5898, 
    5908, 
    5926, 
    5936, 
    5948, 
    5957, 
    5981, 
    5991, 
    6001, 
    6017, 
    6029, 
    6034, 
    6041, 
    6063, 
    6071, 
    6089, 
    6105, 
    6126, 
    6147, 
    6682, 
    6700, 
    6723, 
    6738, 
    6753, 
    6775, 
    6790, 
    6802, 
    6816, 
    6831, 
    6838, 
    6858, 
    6880, 
    6899, 
    6918, 
    5690, 
    5749, 
    5759, 
    5765, 
    5774, 
    5786, 
    5792, 
    5801, 
    7105, 
    7118, 
    7177, 
    7195, 
    5725, 
    5737, 
    7208, 
    7218, 
    7233, 
    7252, 
    7259, 
    5818, 
    5826, 
    5837, 
    5850, 
    6937, 
    6954, 
    6971, 
    6990, 
    7005, 
    7024, 
    5682, 
    5693, 
    5696, 
    5701, 
    5707, 
    5730, 
    5770, 
    5780, 
    5800, 
    5814, 
    5822, 
    5834, 
    5847, 
    5590, 
    5596, 
    5603, 
    5651, 
    5666, 
    5673, 
    5678, 
    5713, 
    5721, 
    5728, 
    5738, 
    5767, 
    5784, 
    5797, 
    5806, 
    5835, 
    5848, 
    5858, 
    5868, 
    5877, 
    5887, 
    5897, 
    5905, 
    5918, 
    5930, 
    5941, 
    5950, 
    5963, 
    5983, 
    6000, 
    6011, 
    6031, 
    6036, 
    6077, 
    6096, 
    6109, 
    6132, 
    6148, 
    6163, 
    6178, 
    6190, 
    6211, 
    6226, 
    6242, 
    6256, 
    6274, 
    6287, 
    6300, 
    6322, 
    6337, 
    6347, 
    6360, 
    6372, 
    6391, 
    6401, 
    6413, 
    6444, 
    6457, 
    6466, 
    6478, 
    6491, 
    6507, 
    6516, 
    6529, 
    6539, 
    6553, 
    6572, 
    6588, 
    6610, 
    6626, 
    6647, 
    5857, 
    5865, 
    5880, 
    5889, 
    5899, 
    5909, 
    5923, 
    5932, 
    5970, 
    5992, 
    6043, 
    6064, 
    6079, 
    6134, 
    6267, 
    6279, 
    6292, 
    6311, 
    6325, 
    6358, 
    6368, 
    6392, 
    6409, 
    6429, 
    6441, 
    6455, 
    6468, 
    6484, 
    6496, 
    6505, 
    6527, 
    6550, 
    6565, 
    6584, 
    6604, 
    6638, 
    6651, 
    6670, 
    6680, 
    6697, 
    6705, 
    6739, 
    6757, 
    6772, 
    6794, 
    6852, 
    6829, 
    6895, 
    6921, 
    6939, 
    6959, 
    6976, 
    6994, 
    7022, 
    7093, 
    7128, 
    7156, 
    7175, 
    7186, 
    7204, 
    7217, 
    7238, 
    7374, 
    7385, 
    7561, 
    7572, 
    7587, 
    7593, 
    7503, 
    7510, 
    7513, 
    7516, 
    7519, 
    7520, 
    7521, 
    7524, 
    7526, 
    7534, 
    7535, 
    7536, 
    7537, 
    5526, 
    6196, 
    6231, 
    6252, 
    6269, 
    6282, 
    6297, 
    6318, 
    6329, 
    6344, 
    6356, 
    6369, 
    6376, 
    6432, 
    6446, 
    6460, 
    6470, 
    6477, 
    6486, 
    6497, 
    6503, 
    6511, 
    6517, 
    6523, 
    6533, 
    6546, 
    6557, 
    6586, 
    6602, 
    6611, 
    6623, 
    6640, 
    6657, 
    6671, 
    6707, 
    6732, 
    6751, 
    6766, 
    6778, 
    6789, 
    6817, 
    6826, 
    6833, 
    6843, 
    6849, 
    6861, 
    8986, 
    6887, 
    6901, 
    6912, 
    6922, 
    6940, 
    6952, 
    6970, 
    6985, 
    6998, 
    7007, 
    7016, 
    7044
  ) 
  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 = 707 
  AND products.company_id IN ('707') 
  AND products.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 products.company_id = 707 
GROUP BY 
  product_id 
ORDER BY 
  popularity.total desc, 
  products.product_id ASC

Query time 0.08721

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6990.52"
    },
    "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": "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": 500,
                "rows_produced_per_join": 20,
                "filtered": "4.00",
                "cost_info": {
                  "read_cost": "698.20",
                  "eval_cost": "4.00",
                  "prefix_cost": "702.20",
                  "data_read_per_join": "118K"
                },
                "used_columns": [
                  "category_id",
                  "usergroup_ids",
                  "status",
                  "storefront_id"
                ],
                "attached_condition": "((`webmarco`.`cscart_categories`.`category_id` in (5372,5495,5506,5511,5519,5525,5612,5639,5647,5652,5661,8981,8972,8973,8974,8975,8976,8977,8978,8979,8980,5672,8957,8958,8959,8960,8961,8962,8963,8964,8965,8966,8967,8968,8969,8970,8971,5577,5584,5599,5608,5619,5623,5632,5640,5649,5655,5662,5669,5675,5686,5694,5706,5712,5719,5726,5734,5743,5752,5763,5771,5778,5796,5809,5817,5828,5840,5851,5863,5871,5878,5891,5904,5917,5925,5933,6113,6141,6153,6104,6123,6142,6173,6215,6235,6271,6278,6289,6302,6380,6396,6407,6421,6437,6450,6461,6469,6479,6492,6509,6515,6520,6537,6548,6558,6569,6585,6606,6624,6645,6672,6687,6702,6721,6736,6750,6771,6793,6811,6822,6839,6853,6866,6874,6888,6949,6969,6993,7010,7046,7065,7084,7101,7121,7147,8949,8950,8951,8952,8784,8790,8791,8793,8794,8795,8796,8801,8806,8824,8826,8827,8828,8829,8830,8797,8799,8802,8804,8807,8808,8809,8811,8813,8798,8800,8803,8805,8810,8812,8814,8815,8816,8819,8821,8817,8818,8820,8822,8823,8825,8831,8832,8833,8834,8835,8836,8837,8838,8839,8840,8841,8842,8843,8844,8845,8846,8847,8848,8849,8850,8953,8954,8955,8956,8945,8946,8947,8948,5630,5860,5869,5656,5881,5898,5908,5926,5936,5948,5957,5981,5991,6001,6017,6029,6034,6041,6063,6071,6089,6105,6126,6147,6682,6700,6723,6738,6753,6775,6790,6802,6816,6831,6838,6858,6880,6899,6918,5690,5749,5759,5765,5774,5786,5792,5801,7105,7118,7177,7195,5725,5737,7208,7218,7233,7252,7259,5818,5826,5837,5850,6937,6954,6971,6990,7005,7024,5682,5693,5696,5701,5707,5730,5770,5780,5800,5814,5822,5834,5847,5590,5596,5603,5651,5666,5673,5678,5713,5721,5728,5738,5767,5784,5797,5806,5835,5848,5858,5868,5877,5887,5897,5905,5918,5930,5941,5950,5963,5983,6000,6011,6031,6036,6077,6096,6109,6132,6148,6163,6178,6190,6211,6226,6242,6256,6274,6287,6300,6322,6337,6347,6360,6372,6391,6401,6413,6444,6457,6466,6478,6491,6507,6516,6529,6539,6553,6572,6588,6610,6626,6647,5857,5865,5880,5889,5899,5909,5923,5932,5970,5992,6043,6064,6079,6134,6267,6279,6292,6311,6325,6358,6368,6392,6409,6429,6441,6455,6468,6484,6496,6505,6527,6550,6565,6584,6604,6638,6651,6670,6680,6697,6705,6739,6757,6772,6794,6852,6829,6895,6921,6939,6959,6976,6994,7022,7093,7128,7156,7175,7186,7204,7217,7238,7374,7385,7561,7572,7587,7593,7503,7510,7513,7516,7519,7520,7521,7524,7526,7534,7535,7536,7537,5526,6196,6231,6252,6269,6282,6297,6318,6329,6344,6356,6369,6376,6432,6446,6460,6470,6477,6486,6497,6503,6511,6517,6523,6533,6546,6557,6586,6602,6611,6623,6640,6657,6671,6707,6732,6751,6766,6778,6789,6817,6826,6833,6843,6849,6861,8986,6887,6901,6912,6922,6940,6952,6970,6985,6998,7007,7016,7044)) 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": 3589,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "25.23",
                  "eval_cost": "717.83",
                  "prefix_cost": "1445.26",
                  "data_read_per_join": "56K"
                },
                "used_columns": [
                  "product_id",
                  "category_id"
                ]
              }
            },
            {
              "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": 897,
                "filtered": "25.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "3589.16",
                  "eval_cost": "179.46",
                  "prefix_cost": "5752.25",
                  "data_read_per_join": "56K"
                },
                "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": "products",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "status",
                  "idx_parent_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": 44,
                "filtered": "5.00",
                "cost_info": {
                  "read_cost": "897.30",
                  "eval_cost": "8.97",
                  "prefix_cost": "6829.00",
                  "data_read_per_join": "350K"
                },
                "used_columns": [
                  "product_id",
                  "product_type",
                  "status",
                  "company_id",
                  "usergroup_ids",
                  "parent_product_id"
                ],
                "attached_condition": "((`webmarco`.`products`.`company_id` = 707) and (`webmarco`.`products`.`company_id` = '707') and (`webmarco`.`products`.`status` = 'A') and ((`webmarco`.`products`.`usergroup_ids` = '') or find_in_set(0,`webmarco`.`products`.`usergroup_ids`) or find_in_set(1,`webmarco`.`products`.`usergroup_ids`)))"
              }
            },
            {
              "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": 44,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "44.86",
                  "eval_cost": "8.97",
                  "prefix_cost": "6882.84",
                  "data_read_per_join": "7K"
                },
                "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": "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": 44,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "44.86",
                  "eval_cost": "8.97",
                  "prefix_cost": "6936.68",
                  "data_read_per_join": "1K"
                },
                "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": 44,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "44.86",
                  "eval_cost": "8.97",
                  "prefix_cost": "6990.52",
                  "data_read_per_join": "306K"
                },
                "used_columns": [
                  "product_id",
                  "lang_code",
                  "product"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Result

product_id	product	company_name	product_ids	product_types	product_type	booking_type
415310	Electric sewing machine with pedal Mini Sewing Machine SM-202A	1001produse.ro	415310	P	P	N
428033	Mini Ventilator de Masa, 3 Trepte de Viteza, Reincarcabil USB, Unghi reglabil	1001produse.ro	428033	P	P
427778	Umidificator, purificator aer, difuzor, aromaterapie, ultrasunete	1001produse.ro	427778	P	P	N