SELECT 
  cscart_categories.category_id, 
  cscart_categories.parent_id, 
  cscart_categories.id_path, 
  cscart_category_descriptions.category, 
  cscart_categories.position, 
  cscart_categories.status, 
  cscart_categories.company_id, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path 
FROM 
  cscart_categories 
  LEFT JOIN cscart_category_descriptions ON cscart_categories.category_id = cscart_category_descriptions.category_id 
  AND cscart_category_descriptions.lang_code = 'ro' 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_categories.category_id 
  AND cscart_seo_names.type = 'c' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'ro' 
  AND cscart_seo_names.company_id = cscart_categories.company_id 
WHERE 
  1 
  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') 
  AND cscart_categories.id_path LIKE '289/%' 
  AND cscart_categories.company_id = 1 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00375

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "84.29"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "range",
            "possible_keys": [
              "c_status",
              "id_path"
            ],
            "key": "id_path",
            "used_key_parts": [
              "id_path"
            ],
            "key_length": "767",
            "rows_examined_per_scan": 96,
            "rows_produced_per_join": 0,
            "filtered": "1.00",
            "index_condition": "(`maniamall_cscart`.`cscart_categories`.`id_path` like '289/%')",
            "cost_info": {
              "read_cost": "82.16",
              "eval_cost": "0.10",
              "prefix_cost": "82.26",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash"
            ],
            "attached_condition": "((`maniamall_cscart`.`cscart_categories`.`company_id` = 1) 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` = 'A'))"
          }
        },
        {
          "table": {
            "table_name": "cscart_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "maniamall_cscart.cscart_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.89",
              "eval_cost": "0.10",
              "prefix_cost": "83.24",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_seo_names",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code",
              "company_id"
            ],
            "key_length": "210",
            "ref": [
              "maniamall_cscart.cscart_categories.category_id",
              "const",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.95",
              "eval_cost": "0.10",
              "prefix_cost": "84.29",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id parent_id id_path category position status company_id seo_name seo_path
29952 289 289/29952 Cupe, Trofee si Medalii 0 A 1 cupe-trofee-si-medalii 289
7168 289 289/7168 Piscine 10 A 1 piscine 289
5549 289 289/5549 Accesorii Piscine 11 A 1 accesorii-piscine 289
2545 289 289/2545 Saltele si Canapele Gonflabile 12 A 1 saltele-canapele-gonflabile 289
2546 289 289/2546 Articole pentru Plaja 13 A 1 articole-pentru-plaja 289
7167 289 289/7167 Trambuline 14 A 1 trambuline 289
881 289 289/881 Articole Camping si Drumetii 15 A 1 articole-camping-si-drumetii 289
7219 289 289/7219 Cutii si Lazi Frigorifice 15 A 1 cutii-lazi-frigorifice 289
5715 289 289/5715 Trotinete 16 A 1 trotinete 289
5442 289 289/5442 Skateboard Penny Board 17 A 1 skateboard-penny-board 289
6941 289 289/6941 Scaune Copii pentru Bicicleta 18 A 1 scaune-copii-pentru-bicicleta-ro 289
28993 28991 289/28991/28993 Accesorii Bicicleta 20 A 1 accesorii-bicicleta-ro-2 289/28991
1162 289 289/1162 Accesorii Bicicleta 90 A 1 accesorii-bicicleta 289
929 881 289/881/929 Accesorii Camping 90 A 1 accesorii-camping 289/881
16597 289 289/16597 accesorii Camping Si Drumetii 90 A 1 accesorii-camping-si-drumetii 289
16604 289 289/16604 accesorii Ciclism 90 A 1 accesorii-ciclism 289
15692 289 289/15692 accesorii Gratar 90 A 1 accesorii-gratar 289
6394 6382 289/6382/6394 Accesorii Pescuit 90 A 1 accesorii-pescuit-ro 289/6382
15711 289 289/15711 accesorii Piscina 90 A 1 accesorii-piscina 289
29508 289 289/29508 Accesorii Sport 90 A 1 accesorii-sport 289
6447 6403 289/6403/6447 Alte Articole de Incaltaminte 90 A 1 alte-articole-de-incaltaminte 289/6403
5706 289 289/5706 Alte Sporturi 90 A 1 alte-sporturi 289
3945 1162 289/1162/3945 Altele 90 A 1 altele 289/1162
29000 28999 289/28991/28993/28999/29000 Angrenaj Pedalier 90 A 1 angrenaj-pedalier 289/28991/28993/28999
28996 28993 289/28991/28993/28996 Antifurturi 90 A 1 antifurturi-ro 289/28991/28993
6633 1095 289/1095/6633 Aparate de Facut Tigari 90 A 1 aparate-de-facut-tigari 289/1095
29002 28993 289/28991/28993/29002 Aparatori Lant 90 A 1 aparatori-lant 289/28991/28993
29003 28993 289/28991/28993/29003 Aripi 90 A 1 aripi 289/28991/28993
2548 1095 289/1095/2548 Articole Autoaparare 90 A 1 articole-autoaparare 289/1095
2620 881 289/881/2620 Articole Calatorii 90 A 1 articole-calatorii 289/881
6382 289 289/6382 Articole Pescuit 90 A 1 articole-pescuit 289
6431 289 289/6431 Articole Vanatoare 90 A 1 articole-vanatoare 289
15718 289 289/15718 arzatoare Camping 90 A 1 arzatoare-camping 289
16612 289 289/16612 aspiratoare Piscina 90 A 1 aspiratoare-piscina 289
29004 28993 289/28991/28993/29004 Ax 90 A 1 ax 289/28991/28993
9646 289 289/9646 Barci Gonflabile 90 A 1 barci-gonflabile 289
6397 6382 289/6382/6397 Barci Motoare si Sonare 90 A 1 barci-motoare-si-sonare 289/6382
8786 289 289/8786 Biciclete 90 A 1 biciclete 289
6848 881 289/881/6848 Busole 90 A 1 busole-ro 289/881
29008 28993 289/28991/28993/29008 Butuci 90 A 1 butuci 289/28991/28993
28998 28993 289/28991/28993/28998 Cablu Accesorii Masurare Viteza 90 A 1 cablu-accesorii-masurare-viteza 289/28991/28993
917 915 289/881/915/917 Cani Termos 90 A 1 cani-termos 289/881/915
2494 289 289/2494 Carucioare pentru Cumparaturi 90 A 1 carucioare-pentru-cumparaturi 289
30406 28993 289/28991/28993/30406 Casca Bicicleta 90 A 1 casca-bicicleta 289/28991/28993
29006 28993 289/28991/28993/29006 Catadioptrii 90 A 1 catadioptrii 289/28991/28993
29001 28993 289/28991/28993/29001 Cauciucuri si Camere 90 A 1 cauciucuri-si-camere 289/28991/28993
16599 289 289/16599 colaci Ochelari Si Accesorii Inot Copii 90 A 1 colaci-ochelari-si-accesorii-inot-copii 289
30497 30487 289/28991/28993/30487/30497 Coronite - Pene 90 A 1 coronite-pene 289/28991/28993/30487
5770 881 289/881/5770 Corturi 90 A 1 corturi 289/881
16587 289 289/16587 corturi Camping 90 A 1 corturi-camping 289
2526 1095 289/1095/2526 Diverse Hobby 90 A 1 diverse-hobby 289/1095
2784 289 289/2784 Drone 90 A 1 drone 289
6403 289 289/6403 Echipamente 90 A 1 echipamente 289
29012 28993 289/28991/28993/29012 Frane, Manete, Saboti 90 A 1 frane-manete-saboti 289/28991/28993
29020 28993 289/28991/28993/29020 Furci 90 A 1 furci-ro 289/28991/28993
933 881 289/881/933 Genti Frigorifice 90 A 1 genti-frigorifice 289/881
6366 881 289/881/6366 Genti si Trolere Voiaj 90 A 1 genti-si-trolere-voiaj 289/881
29011 28993 289/28991/28993/29011 Genti, Borsete 90 A 1 genti-borsete 289/28991/28993
1095 289 289/1095 Hobby 90 A 1 hobby 289
29021 28993 289/28991/28993/29021 Iluminat Bicicleta 90 A 1 iluminat-bicicleta 289/28991/28993
15688 289 289/15688 lanterne 90 A 1 lanterne-ro-2 289
29016 28993 289/28991/28993/29016 Lanturi 90 A 1 lanturi 289/28991/28993
29013 28993 289/28991/28993/29013 Mansoane 90 A 1 mansoane-ro 289/28991/28993
6444 6403 289/6403/6444 Manusi 90 A 1 manusi-ro 289/6403
7226 289 289/7226 Mese de Tenis Ping-Pong 90 A 1 mese-de-tenis-ping-pong 289
29005 28999 289/28991/28993/28999/29005 Monobloc Pedalier 90 A 1 monobloc-pedalier 289/28991/28993/28999
6406 6403 289/6403/6406 Ochelari 90 A 1 ochelari-ro 289/6403
6640 6403 289/6403/6640 Pantaloni 90 A 1 pantaloni-ro-2 289/6403
28999 28993 289/28991/28993/28999 Pedale 90 A 1 pedale 289/28991/28993
30487 28993 289/28991/28993/30487 Piese De Schimb 90 A 1 piese-de-schimb 289/28991/28993
29028 28993 289/28991/28993/29028 Pinioane 90 A 1 pinioane 289/28991/28993
16592 289 289/16592 piscine Copii 90 A 1 piscine-copii 289
15699 289 289/15699 pompe De Umflat 90 A 1 pompe-de-umflat 289
29018 28993 289/28991/28993/29018 Portbagaje, Cosuri 90 A 1 portbagaje-cosuri 289/28991/28993
8831 289 289/8831 Role si Patine 90 A 1 role-si-patine 289
30504 30487 289/28991/28993/30487/30504 Roti - Jante - Spite 90 A 1 roti-jante-spite 289/28991/28993/30487
29030 28993 289/28991/28993/29030 Roti Ajutatoare 90 A 1 roti-ajutatoare 289/28991/28993
7170 881 289/881/7170 Rucsacuri si Ghiozdane 90 A 1 rucsacuri-si-ghiozdane 289/881
6451 6431 289/6431/6451 Rucsacuri Vanatoare 90 A 1 rucsacuri-vanatoare 289/6431
7227 881 289/881/7227 Saci de Dormit 90 A 1 saci-de-dormit 289/881
16610 289 289/16610 saltele Si Mingi Pentru Plaja 90 A 1 saltele-si-mingi-pentru-plaja 289
14451 289 289/14451 Saniute Copii 90 A 1 saniute-copii 289
29031 28993 289/28991/28993/29031 Scaune Copii 90 A 1 scaune-copii 289/28991/28993
29015 28993 289/28991/28993/29015 Scule Si Intretinere 90 A 1 scule-si-intretinere 289/28991/28993
30494 30487 289/28991/28993/30487/30494 Sei - Tije - Arcuri 90 A 1 sei-tije-arcuri 289/28991/28993/30487
6442 6403 289/6403/6442 Sepci Palarii si Caciuli 90 A 1 sepci-palarii-si-caciuli 289/6403
29022 28993 289/28991/28993/29022 Sonerii 90 A 1 sonerii 289/28991/28993
29007 28993 289/28991/28993/29007 Sticle si Recipiente 90 A 1 sticle-si-recipiente 289/28991/28993
915 881 289/881/915 Termosuri 90 A 1 termosuri-ro 289/881
916 915 289/881/915/916 Termosuri 90 A 1 termosuri 289/881/915
1096 1095 289/1095/1096 Tigari Electronice 90 A 1 tigari-electronice 289/1095
30488 30487 289/28991/28993/30487/30488 Valve 90 A 1 valve 289/28991/28993/30487
28991 289 289/28991 Biciclete si Accesorii 100 A 1 biciclete-si-accesorii 289
7379 6403 289/6403/7379 Cagule 100 A 1 cagule 289/6403
11406 6431 289/6431/11406 Lunete si munitie 100 A 1 lunete-si-munitie 289/6431
29713 289 289/29713 Tobogane pentru copii 110 A 1 tobogane-pentru-copii 289