SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.product, descr1.product
  ) as product, 
  stoc_intern, 
  pret_achizitie, 
  pret_revanzator, 
  gestiune 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_options_inventory as inventory ON inventory.product_id = products.product_id 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'ro' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  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') 
  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 = 'ro' 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
WHERE 
  1 
  AND cscart_categories.category_id IN (
    297, 
    1000, 
    1001, 
    1156, 
    21656, 
    21681, 
    2920, 
    31174, 
    1188, 
    1199, 
    1005, 
    1006, 
    1040, 
    1139, 
    1161, 
    1193, 
    1194, 
    1195, 
    1218, 
    6853, 
    7129, 
    8779, 
    1140, 
    1166, 
    1167, 
    1220, 
    7344, 
    1176, 
    1181, 
    2718, 
    6991, 
    1026, 
    1008, 
    1042, 
    1043, 
    30200, 
    7007, 
    1039, 
    10495, 
    1048, 
    2849, 
    1051, 
    1089, 
    1097, 
    1100, 
    1101, 
    6583, 
    1128, 
    1129, 
    2531, 
    1163, 
    1221, 
    1863, 
    1346, 
    1685, 
    1688, 
    1871, 
    2405, 
    2724, 
    2845, 
    1171, 
    1190, 
    41731, 
    8914, 
    1191, 
    1192, 
    1302, 
    1197, 
    1198, 
    1239, 
    1240, 
    1372, 
    1204, 
    1309, 
    1338, 
    1339, 
    1352, 
    1362, 
    1371, 
    1369, 
    1359, 
    1380, 
    1383, 
    1570, 
    15709, 
    15736, 
    16598, 
    16600, 
    16605, 
    16606, 
    16621, 
    16622, 
    16631, 
    17332, 
    19138, 
    19142, 
    19143, 
    19311, 
    21611, 
    19212, 
    19213, 
    21672, 
    17349, 
    17426, 
    19202, 
    19264, 
    19280, 
    17643, 
    27275, 
    27871, 
    17700, 
    19301, 
    19318, 
    2534, 
    2558, 
    3112, 
    3571, 
    7218, 
    2597, 
    26441, 
    2646, 
    2968, 
    36889, 
    37371, 
    37723, 
    39736, 
    39723, 
    39724, 
    4441, 
    4557, 
    4558, 
    4559, 
    4560, 
    5027, 
    5041, 
    10531, 
    10583, 
    10647, 
    10746, 
    5043, 
    5049, 
    5053, 
    5065, 
    5066, 
    5154, 
    5176, 
    5451, 
    5454, 
    5565, 
    6379, 
    6606, 
    6636, 
    6637, 
    6840, 
    6854, 
    862, 
    1033, 
    1037, 
    1113, 
    1133, 
    1215, 
    2555, 
    2856, 
    863, 
    8797, 
    935, 
    1032, 
    1153, 
    1157, 
    1172, 
    2978, 
    5156, 
    2553
  ) 
  AND cscart_categories.company_id = 1 
  AND (
    CASE products.tracking WHEN 'O' THEN inventory.amount > 0 WHEN 'B' THEN products.amount > 0 ELSE 1 END
  ) 
  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 
  products.product_id 
ORDER BY 
  popularity.total desc, 
  product_id ASC 
LIMIT 
  0, 128

Query time 0.02375

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "185.62"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "inventory",
              "access_type": "system",
              "possible_keys": [
                "pc"
              ],
              "rows_examined_per_scan": 0,
              "rows_produced_per_join": 1,
              "filtered": "0.00",
              "const_row_not_found": true,
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.10",
                "prefix_cost": "0.00",
                "data_read_per_join": "880"
              },
              "used_columns": [
                "product_id",
                "amount"
              ]
            }
          },
          {
            "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": 173,
              "rows_produced_per_join": 3,
              "filtered": "2.00",
              "cost_info": {
                "read_cost": "164.53",
                "eval_cost": "0.35",
                "prefix_cost": "164.88",
                "data_read_per_join": "8K"
              },
              "used_columns": [
                "category_id",
                "company_id",
                "usergroup_ids",
                "status"
              ],
              "attached_condition": "((`maniamall_cscart`.`cscart_categories`.`company_id` = 1) and (`maniamall_cscart`.`cscart_categories`.`category_id` in (297,1000,1001,1156,21656,21681,2920,31174,1188,1199,1005,1006,1040,1139,1161,1193,1194,1195,1218,6853,7129,8779,1140,1166,1167,1220,7344,1176,1181,2718,6991,1026,1008,1042,1043,30200,7007,1039,10495,1048,2849,1051,1089,1097,1100,1101,6583,1128,1129,2531,1163,1221,1863,1346,1685,1688,1871,2405,2724,2845,1171,1190,41731,8914,1191,1192,1302,1197,1198,1239,1240,1372,1204,1309,1338,1339,1352,1362,1371,1369,1359,1380,1383,1570,15709,15736,16598,16600,16605,16606,16621,16622,16631,17332,19138,19142,19143,19311,21611,19212,19213,21672,17349,17426,19202,19264,19280,17643,27275,27871,17700,19301,19318,2534,2558,3112,3571,7218,2597,26441,2646,2968,36889,37371,37723,39736,39723,39724,4441,4557,4558,4559,4560,5027,5041,10531,10583,10647,10746,5043,5049,5053,5065,5066,5154,5176,5451,5454,5565,6379,6606,6636,6637,6840,6854,862,1033,1037,1113,1133,1215,2555,2856,863,8797,935,1032,1153,1157,1172,2978,5156,2553)) and ((`maniamall_cscart`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`maniamall_cscart`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`maniamall_cscart`.`cscart_categories`.`usergroup_ids`))) and (`maniamall_cscart`.`cscart_categories`.`status` in ('A','H')))"
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "maniamall_cscart.cscart_categories.category_id"
              ],
              "rows_examined_per_scan": 19,
              "rows_produced_per_join": 67,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "2.96",
                "eval_cost": "6.74",
                "prefix_cost": "174.58",
                "data_read_per_join": "1K"
              },
              "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": [
                "maniamall_cscart.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3,
              "filtered": "5.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.34",
                "prefix_cost": "181.32",
                "data_read_per_join": "14K"
              },
              "used_columns": [
                "product_id",
                "status",
                "amount",
                "usergroup_ids",
                "tracking",
                "stoc_intern",
                "pret_achizitie",
                "pret_revanzator",
                "gestiune"
              ],
              "attached_condition": "((0 <> (case `maniamall_cscart`.`products`.`tracking` when 'O' then <cache>((NULL > 0)) when 'B' then (`maniamall_cscart`.`products`.`amount` > 0) else 1 end)) and ((`maniamall_cscart`.`products`.`usergroup_ids` = '') or (0 <> find_in_set(0,`maniamall_cscart`.`products`.`usergroup_ids`)) or (0 <> find_in_set(1,`maniamall_cscart`.`products`.`usergroup_ids`))) and (`maniamall_cscart`.`products`.`status` = 'A'))"
            }
          },
          {
            "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": [
                "maniamall_cscart.products_categories.product_id",
                "const",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.34",
                "prefix_cost": "181.66",
                "data_read_per_join": "12K"
              },
              "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": [
                "maniamall_cscart.products_categories.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.34",
                "prefix_cost": "182.00",
                "data_read_per_join": "12K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product"
              ]
            }
          },
          {
            "table": {
              "table_name": "popularity",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "total"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "maniamall_cscart.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.34",
                "prefix_cost": "182.33",
                "data_read_per_join": "107"
              },
              "used_columns": [
                "product_id",
                "total"
              ]
            }
          },
          {
            "table": {
              "table_name": "prices",
              "access_type": "ref",
              "possible_keys": [
                "usergroup",
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "key": "lower_limit",
              "used_key_parts": [
                "lower_limit",
                "product_id"
              ],
              "key_length": "6",
              "ref": [
                "const",
                "maniamall_cscart.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "50.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "2.95",
                "eval_cost": "0.17",
                "prefix_cost": "185.62",
                "data_read_per_join": "40"
              },
              "used_columns": [
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "attached_condition": "(`maniamall_cscart`.`prices`.`usergroup_id` in (0,0,1))"
            }
          }
        ]
      }
    }
  }
}

Result

product_id product stoc_intern pret_achizitie pret_revanzator gestiune
1080078 Cooler laptop 9-17", reglabil, 5 ventilatoare, 2xusb, cu ecran LCD, negru -99 65.12 78.14 Y
480529 Proiector Lampa Rotunda pentru Studio Foto, Putere 40W, Lumina Alb Rece 5600k -99 180.99 213.00 Y
663018 Telecomanda Universala Programabila pe Frecventa Radio 433Mh, Raza Acoperire 70m -99 15.24 18.28 Y
562184 Boxa Bluetooth Activa Portabila Blow cu Microfon si Telecomanda, Functie Karaoke, Putere 100W, Radio FM, USB, AUX, Card SD -99 480.00 576.00 Y
521242 Clopotel Sonerie pentru Receptie Hoteluri, Baruri, Restaurante, Pensiuni -99 5.00 18.00 Y
521141 Adaptor Lightning de la Micro USB la USB-C 3.0 - 3.1, culoare Alb -99 1.00 5.00 Y
481524 Kit Lumina Continua Softbox pentru Studio Foto sau Videochat cu 1 Bec E27 Inclus -99 166.00 135.00 Y
866373 Incarcator auto wireless, cu suport inchidere/deschidere automata, incarcare prin inductie electromagnetica, brat reglabil, negru -99 56.78 68.13 Y
525844 Cablu Video HDMI HD3 - Micro HDMI 1.4, Lungime 1.5m -99 7.53 25.00 Y
431448 Cablu Video HDMI High Speed, Lungime 5m -99 10.74 36.00 Y
522508 Incarcator Rapid Wireless Rotund Qi LED Universal Fast Charger + Cablu Micro USB -99 46.01 79.00 Y
880662 Incarcator auto cu 2 USB si cablu Lightning pentru iPhone sau iPad, 2.4A -99 26.26 29.61 Y
522509 Incarcator Rapid Wireless Oval Qi Universal Fast Charger + Cablu Micro USB -99 52.44 85.00 Y
431449 Cablu Video HDMI High Speed, Lungime 2m -99 8.31 26.00 Y
1313291 Suport Universal pentru Tablete 7-10", pliabil, culoare negru -99 28.28 33.94 Y
487149 Cablu Prelungitor Extensie pentru USB, Lungime 10m -99 21.52 45.00 Y
484141 Kit Cabluri Auto pentru Conectare Audio Video Blow MT-X100 -99 36.09 58.00 Y
740234 Dispozitiv de Numarare Mecanic cu Clicker, Negru -99 5.32 0.00 Y
740227 Lentila Telescop Portabil cu Suport Trepied pentru Telefon, Marire 10x -99 47.57 0.00 Y
1121453 Convertor adaptor semnal HDMI tata la VGA mama -99 9.89 11.87 Y
525843 Cablu Video HDMI 1.4A 3D HighSpeed, Lungime 1.5m -99 4.95 19.00 Y
822713 Incarcator wireless cu inductie Blow WCH-01, 1500 mA, protectie scurtcircuit, negru -99 8.74 10.49 Y
822715 Incarcator wireless cu inductie Blow WCH-04, 1500 mA, cu suport si protectie la scurtcircuit -99 34.90 41.88 Y
2047822 Mini Microfon pentru Telefon cu Fir 1.5 m, 5.8x1.3x1.7 cm, Argintiu -99 13.79 25.00 Y
822658 Cablu HDMI-HDMI Blow, Lungime 3 m, culoare albastru -99 14.68 17.62 Y
2077218 Busola pentru harta, rigla, lupa si agatatoare, ideala pentru calatorii -99 8.26 26.00 Y
671322 Antena detasabila Wireless USB 2.0 sau 1.1, 300 Mbps Wifi -99 12.19 32.00 Y
822656 Cablu prelungitor alimentare PC sau monitor, lungime 1.5m, culoare negru -99 5.14 6.17 Y
2014474 Casti Bluetooth cu Statie de Incarcare si Microfon, Raza de Actiune 10m, culoare Negru -99 59.99 0.00 Y
1153208 Casti de Gaming Blow cu microfon, cablu 2.4m, negru/verde -99 24.71 29.65 Y