SELECT 
  cfv.category_filter_value_id, 
  cfv.parent_id, 
  cfv.parent_name, 
  cfv.order, 
  cfvd.title, 
  cfv.hint, 
  cfv.is_any, 
  cfvd.lang_code 
FROM 
  cscart_category_filter_values AS cfv 
  LEFT JOIN cscart_category_filter_value_descriptions AS cfvd ON cfv.category_filter_value_id = cfvd.category_filter_value_id 
  AND cfvd.lang_code = 'de' 
WHERE 
  1 
  AND cfv.parent_id IN (3) 
  AND cfv.parent_name IN ('filter_values_set') 
ORDER BY 
  cfv.order ASC

Query time 0.00278

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "cfv.`order`",
        "table": {
          "table_name": "cfv",
          "access_type": "ALL",
          "rows": 1012,
          "filtered": 100,
          "attached_condition": "cfv.parent_id = 3 and cfv.parent_name = 'filter_values_set'"
        }
      }
    },
    "table": {
      "table_name": "cfvd",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "10",
      "used_key_parts": ["category_filter_value_id", "lang_code"],
      "ref": ["dev.cfv.category_filter_value_id", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cfvd.lang_code = 'de')"
    }
  }
}

Result

category_filter_value_id parent_id parent_name order title hint is_any lang_code
967 3 filter_values_set 0 Any realm Y de
84 3 filter_values_set 1 Aegwynn N de
85 3 filter_values_set 2 Aerie Peak N de
86 3 filter_values_set 3 Agamaggan N de
87 3 filter_values_set 4 Aggra (Português) N de
88 3 filter_values_set 5 Aggramar N de
89 3 filter_values_set 6 Ahn'Qiraj N de
90 3 filter_values_set 7 Al'Akir N de
91 3 filter_values_set 8 Alexstrasza N de
92 3 filter_values_set 9 Alleria N de
93 3 filter_values_set 10 Alonsus N de
94 3 filter_values_set 11 Aman'Thul N de
95 3 filter_values_set 12 Ambossar N de
96 3 filter_values_set 13 Anachronos N de
97 3 filter_values_set 14 Anetheron N de
98 3 filter_values_set 15 Antonidas N de
99 3 filter_values_set 16 Anub'arak N de
100 3 filter_values_set 17 Arak-arahm N de
101 3 filter_values_set 18 Arathi N de
102 3 filter_values_set 19 Arathor N de
103 3 filter_values_set 20 Archimonde N de
104 3 filter_values_set 21 Area 52 N de
105 3 filter_values_set 22 Argent Dawn N de
106 3 filter_values_set 23 Arthas N de
107 3 filter_values_set 24 Arygos N de
108 3 filter_values_set 25 Aszune N de
109 3 filter_values_set 26 Auchindoun N de
110 3 filter_values_set 27 Azjol-Nerub N de
111 3 filter_values_set 28 Azshara N de
112 3 filter_values_set 29 Azuremyst N de
113 3 filter_values_set 30 Baelgun N de
114 3 filter_values_set 31 Balnazzar N de
115 3 filter_values_set 32 Blackhand N de
116 3 filter_values_set 33 Blackmoore N de
117 3 filter_values_set 34 Blackrock N de
118 3 filter_values_set 35 Blade's Edge N de
119 3 filter_values_set 36 Bladefist N de
120 3 filter_values_set 37 Bloodfeather N de
121 3 filter_values_set 38 Bloodhoof N de
122 3 filter_values_set 39 Bloodscalp N de
123 3 filter_values_set 40 Blutkessel N de
124 3 filter_values_set 41 Boulderfist N de
125 3 filter_values_set 42 Bronze Dragonflight N de
126 3 filter_values_set 43 Bronzebeard N de
127 3 filter_values_set 44 Burning Blade N de
128 3 filter_values_set 45 Burning Legion N de
129 3 filter_values_set 46 Burning Steppes N de
130 3 filter_values_set 47 C'Thun N de
131 3 filter_values_set 48 Chamber of Aspects N de
132 3 filter_values_set 49 Chants éternels N de
133 3 filter_values_set 50 Cho'gall N de
134 3 filter_values_set 51 Chromaggus N de
135 3 filter_values_set 52 Confrérie du Thorium N de
136 3 filter_values_set 53 Conseil des Ombres N de
137 3 filter_values_set 54 Crushridge N de
138 3 filter_values_set 55 Culte de la Rive noire N de
139 3 filter_values_set 56 Daggerspine N de
140 3 filter_values_set 57 Dalaran N de
141 3 filter_values_set 58 Dalvengyr N de
142 3 filter_values_set 59 Darkmoon Faire N de
143 3 filter_values_set 60 Darksorrow N de
144 3 filter_values_set 61 Darkspear N de
145 3 filter_values_set 62 Das Konsortium N de
146 3 filter_values_set 63 Das Syndikat N de
147 3 filter_values_set 64 Deathwing N de
148 3 filter_values_set 65 Defias Brotherhood N de
149 3 filter_values_set 66 Dentarg N de
150 3 filter_values_set 67 Der Mithrilorden N de
151 3 filter_values_set 68 Der Rat von Dalaran N de
152 3 filter_values_set 69 Der abyssische Rat N de
153 3 filter_values_set 70 Destromath N de
154 3 filter_values_set 71 Dethecus N de
155 3 filter_values_set 72 Die Aldor N de
156 3 filter_values_set 73 Die Arguswacht N de
157 3 filter_values_set 74 Die Nachtwache N de
158 3 filter_values_set 75 Die Silberne Hand N de
159 3 filter_values_set 76 Die Todeskrallen N de
160 3 filter_values_set 77 Die ewige Wacht N de
161 3 filter_values_set 78 Doomhammer N de
162 3 filter_values_set 79 Draenor N de
163 3 filter_values_set 80 Dragonblight N de
164 3 filter_values_set 81 Dragonmaw N de
165 3 filter_values_set 82 Drak'thul N de
166 3 filter_values_set 83 Drek'Thar N de
167 3 filter_values_set 84 Dun Modr N de
168 3 filter_values_set 85 Dun Morogh N de
169 3 filter_values_set 86 Dunemaul N de
170 3 filter_values_set 87 Durotan N de
171 3 filter_values_set 88 Earthen Ring N de
172 3 filter_values_set 89 Echsenkessel N de
173 3 filter_values_set 90 Eitrigg N de
174 3 filter_values_set 91 Eldre'Thalas N de
175 3 filter_values_set 92 Elune N de
176 3 filter_values_set 93 Emerald Dream N de
177 3 filter_values_set 94 Emeriss N de
178 3 filter_values_set 95 Eonar N de
179 3 filter_values_set 96 Eredar N de
180 3 filter_values_set 97 Executus N de
181 3 filter_values_set 98 Exodar N de
182 3 filter_values_set 99 Festung der Stürme N de
183 3 filter_values_set 100 Forscherliga N de
184 3 filter_values_set 101 Frostmane N de
185 3 filter_values_set 102 Frostmourne N de
186 3 filter_values_set 103 Frostwhisper N de
187 3 filter_values_set 104 Frostwolf N de
188 3 filter_values_set 105 Garona N de
189 3 filter_values_set 106 Garrosh N de
190 3 filter_values_set 107 Genjuros N de
191 3 filter_values_set 108 Ghostlands N de
192 3 filter_values_set 109 Gilneas N de
193 3 filter_values_set 110 Gorgonnash N de
194 3 filter_values_set 111 Grim Batol N de
195 3 filter_values_set 112 Gul'dan N de
196 3 filter_values_set 113 Hakkar N de
197 3 filter_values_set 114 Haomarush N de
198 3 filter_values_set 115 Hellfire N de
199 3 filter_values_set 116 Hellscream N de
200 3 filter_values_set 117 Hyjal N de
201 3 filter_values_set 118 Illidan N de
202 3 filter_values_set 119 Jaedenar N de
203 3 filter_values_set 120 Kael'thas N de
204 3 filter_values_set 121 Karazhan N de
205 3 filter_values_set 122 Kargath N de
206 3 filter_values_set 123 Kazzak N de
207 3 filter_values_set 124 Kel'Thuzad N de
208 3 filter_values_set 125 Khadgar N de
209 3 filter_values_set 126 Khaz Modan N de
210 3 filter_values_set 127 Khaz'goroth N de
211 3 filter_values_set 128 Kil'jaeden N de
212 3 filter_values_set 129 Kilrogg N de
213 3 filter_values_set 130 Kirin Tor N de
214 3 filter_values_set 131 Kor'gall N de
215 3 filter_values_set 132 Krag'jin N de
216 3 filter_values_set 133 Krasus N de
217 3 filter_values_set 134 Kul Tiras N de
218 3 filter_values_set 135 Kult der Verdammten N de
219 3 filter_values_set 136 La Croisade écarlate N de
220 3 filter_values_set 137 Laughing Skull N de
221 3 filter_values_set 138 Les Clairvoyants N de
222 3 filter_values_set 139 Les Sentinelles N de
223 3 filter_values_set 140 Lightbringer N de
224 3 filter_values_set 141 Lightning's Blade N de
225 3 filter_values_set 142 Lordaeron N de
226 3 filter_values_set 143 Los Errantes N de
227 3 filter_values_set 144 Lothar N de
228 3 filter_values_set 145 Madmortem N de
229 3 filter_values_set 146 Magtheridon N de
230 3 filter_values_set 147 Mal'Ganis N de
231 3 filter_values_set 148 Malfurion N de
232 3 filter_values_set 149 Malorne N de
233 3 filter_values_set 150 Malygos N de
234 3 filter_values_set 151 Mannoroth N de
235 3 filter_values_set 152 Marécage de Zangar N de
236 3 filter_values_set 153 Mazrigos N de
237 3 filter_values_set 154 Medivh N de
238 3 filter_values_set 155 Minahonda N de
239 3 filter_values_set 156 Moonglade N de
240 3 filter_values_set 157 Mug'thol N de
241 3 filter_values_set 158 Nagrand N de
242 3 filter_values_set 159 Nathrezim N de
243 3 filter_values_set 160 Naxxramas N de
244 3 filter_values_set 161 Nazjatar N de
245 3 filter_values_set 162 Nefarian N de
246 3 filter_values_set 163 Nemesis N de
247 3 filter_values_set 164 Neptulon N de
248 3 filter_values_set 165 Ner'zhul N de
249 3 filter_values_set 166 Nera'thor N de
250 3 filter_values_set 167 Nethersturm N de
251 3 filter_values_set 168 Nordrassil N de
252 3 filter_values_set 169 Norgannon N de
253 3 filter_values_set 170 Nozdormu N de
254 3 filter_values_set 171 Onyxia N de
255 3 filter_values_set 172 Outland N de
256 3 filter_values_set 173 Perenolde N de
257 3 filter_values_set 174 Pozzo dell'Eternità N de
258 3 filter_values_set 175 Proudmoore N de
259 3 filter_values_set 176 Quel'Thalas N de
260 3 filter_values_set 177 Ragnaros N de
261 3 filter_values_set 178 Rajaxx N de
262 3 filter_values_set 179 Rashgarroth N de
263 3 filter_values_set 180 Ravencrest N de
264 3 filter_values_set 181 Ravenholdt N de
265 3 filter_values_set 182 Rexxar N de
266 3 filter_values_set 183 Runetotem N de
267 3 filter_values_set 184 Sanguino N de
268 3 filter_values_set 185 Sargeras N de
269 3 filter_values_set 186 Saurfang N de
270 3 filter_values_set 187 Scarshield Legion N de
271 3 filter_values_set 188 Sen'jin N de
272 3 filter_values_set 189 Shadowsong N de
273 3 filter_values_set 190 Shattered Halls N de
274 3 filter_values_set 191 Shattered Hand N de
275 3 filter_values_set 192 Shattrath N de
276 3 filter_values_set 193 Shen'dralar N de
277 3 filter_values_set 194 Silvermoon N de
278 3 filter_values_set 195 Sinstralis N de
279 3 filter_values_set 196 Skullcrusher N de
280 3 filter_values_set 197 Spinebreaker N de
281 3 filter_values_set 198 Spirestone N de
282 3 filter_values_set 199 Sporeggar N de
283 3 filter_values_set 200 Steamwheedle Cartel N de
284 3 filter_values_set 201 Stormrage N de
285 3 filter_values_set 202 Stormreaver N de
286 3 filter_values_set 203 Stormscale N de
287 3 filter_values_set 204 Sunstrider N de
288 3 filter_values_set 205 Suramar N de
289 3 filter_values_set 206 Sylvanas N de
290 3 filter_values_set 207 Taerar N de
291 3 filter_values_set 208 Talnivarr N de
292 3 filter_values_set 209 Tarren Mill N de
293 3 filter_values_set 210 Teldrassil N de
294 3 filter_values_set 211 Temple noir N de
295 3 filter_values_set 212 Terenas N de
296 3 filter_values_set 213 Terokkar N de
297 3 filter_values_set 214 Terrordar N de
298 3 filter_values_set 215 The Maelstrom N de
299 3 filter_values_set 216 The Sha'tar N de
300 3 filter_values_set 217 The Venture Co N de
301 3 filter_values_set 218 Theradras N de
302 3 filter_values_set 219 Thrall N de
303 3 filter_values_set 220 Throk'Feroth N de
304 3 filter_values_set 221 Thunderhorn N de
305 3 filter_values_set 222 Tichondrius N de
306 3 filter_values_set 223 Tirion N de
307 3 filter_values_set 224 Todeswache N de
308 3 filter_values_set 225 Trollbane N de
309 3 filter_values_set 226 Turalyon N de
310 3 filter_values_set 227 Twilight's Hammer N de
311 3 filter_values_set 228 Twisting Nether N de
312 3 filter_values_set 229 Tyrande N de
313 3 filter_values_set 230 Uldaman N de
314 3 filter_values_set 231 Ulduar N de
315 3 filter_values_set 232 Uldum N de
316 3 filter_values_set 233 Un'Goro N de
317 3 filter_values_set 234 Varimathras N de
318 3 filter_values_set 235 Vashj N de
319 3 filter_values_set 236 Vek'lor N de
320 3 filter_values_set 237 Vek'nilash N de
321 3 filter_values_set 238 Vol'jin N de
322 3 filter_values_set 239 Wildhammer N de
323 3 filter_values_set 240 Wrathbringer N de
324 3 filter_values_set 241 Xavius N de
325 3 filter_values_set 242 Ysera N de
326 3 filter_values_set 243 Ysondre N de
327 3 filter_values_set 244 Zenedar N de
328 3 filter_values_set 245 Zirkel des Cenarius N de
329 3 filter_values_set 246 Zul'jin N de
330 3 filter_values_set 247 Zuluhed N de