Sandbox

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 (15) 
  AND cfv.parent_name IN ('filter_values_set') 
ORDER BY 
  cfv.order ASC

Query time 0.00150

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "cfv.`order`",
        "table": {
          "table_name": "cfv",
          "access_type": "ALL",
          "rows": 1025,
          "filtered": 100,
          "attached_condition": "cfv.parent_id = 15 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	is_any	lang_code
677	15	filter_values_set	0	(EU) Chaos - Cerberus	N	de
678	15	filter_values_set	1	(EU) Chaos - Louisoix	N	de
679	15	filter_values_set	2	(EU) Chaos - Moogle	N	de
680	15	filter_values_set	3	(EU) Chaos - Omega	N	de
681	15	filter_values_set	4	(EU) Chaos - Phantom	N	de
682	15	filter_values_set	5	(EU) Chaos - Ragnarok	N	de
683	15	filter_values_set	6	(EU) Chaos - Sagittarius	N	de
684	15	filter_values_set	7	(EU) Chaos - Spriggan	N	de
685	15	filter_values_set	8	(EU) Light - Alpha	N	de
686	15	filter_values_set	9	(EU) Light - Lich	N	de
687	15	filter_values_set	10	(EU) Light - Odin	N	de
688	15	filter_values_set	11	(EU) Light - Phoenix	N	de
689	15	filter_values_set	12	(EU) Light - Raiden	N	de
690	15	filter_values_set	13	(EU) Light - Shiva	N	de
691	15	filter_values_set	14	(EU) Light - Twintania	N	de
692	15	filter_values_set	15	(EU) Light - Zodiark	N	de
693	15	filter_values_set	16	(JP) Elemental - Aegis	N	de
694	15	filter_values_set	17	(JP) Elemental - Atomos	N	de
695	15	filter_values_set	18	(JP) Elemental - Carbuncle	N	de
696	15	filter_values_set	19	(JP) Elemental - Garuda	N	de
697	15	filter_values_set	20	(JP) Elemental - Gungnir	N	de
698	15	filter_values_set	21	(JP) Elemental - Kujata	N	de
699	15	filter_values_set	22	(JP) Elemental - Ramuh	N	de
700	15	filter_values_set	23	(JP) Elemental - Tonberry	N	de
701	15	filter_values_set	24	(JP) Elemental - Typhon	N	de
702	15	filter_values_set	25	(JP) Elemental - Unicorn	N	de
703	15	filter_values_set	26	(JP) Gaia - Alexander	N	de
704	15	filter_values_set	27	(JP) Gaia - Bahamut	N	de
705	15	filter_values_set	28	(JP) Gaia - Durandal	N	de
706	15	filter_values_set	29	(JP) Gaia - Fenrir	N	de
707	15	filter_values_set	30	(JP) Gaia - Ifrit	N	de
708	15	filter_values_set	31	(JP) Gaia - Ridill	N	de
709	15	filter_values_set	32	(JP) Gaia - Tiamat	N	de
710	15	filter_values_set	33	(JP) Gaia - Ultima	N	de
711	15	filter_values_set	34	(JP) Gaia - Valefor	N	de
712	15	filter_values_set	35	(JP) Gaia - Yojimbo	N	de
713	15	filter_values_set	36	(JP) Gaia - Zeromus	N	de
714	15	filter_values_set	37	(JP) Mana - Anima	N	de
715	15	filter_values_set	38	(JP) Mana - Asura	N	de
716	15	filter_values_set	39	(JP) Mana - Belias	N	de
717	15	filter_values_set	40	(JP) Mana - Chocobo	N	de
718	15	filter_values_set	41	(JP) Mana - Hades	N	de
719	15	filter_values_set	42	(JP) Mana - Ixion	N	de
720	15	filter_values_set	43	(JP) Mana - Mandragora	N	de
721	15	filter_values_set	44	(JP) Mana - Masamune	N	de
722	15	filter_values_set	45	(JP) Mana - Pandaemonium	N	de
723	15	filter_values_set	46	(JP) Mana - Shinryu	N	de
724	15	filter_values_set	47	(JP) Mana - Titan	N	de
725	15	filter_values_set	48	(NA) Aether - Adamantoise	N	de
726	15	filter_values_set	49	(NA) Aether - Cactuar	N	de
727	15	filter_values_set	50	(NA) Aether - Faerie	N	de
728	15	filter_values_set	51	(NA) Aether - Gilgamesh	N	de
729	15	filter_values_set	52	(NA) Aether - Jenova	N	de
730	15	filter_values_set	53	(NA) Aether - Midgardsormr	N	de
731	15	filter_values_set	54	(NA) Aether - Sargatanas	N	de
732	15	filter_values_set	55	(NA) Aether - Siren	N	de
733	15	filter_values_set	56	(NA) Crystal - Balmung	N	de
734	15	filter_values_set	57	(NA) Crystal - Brynhildr	N	de
735	15	filter_values_set	58	(NA) Crystal - Coeurl	N	de
736	15	filter_values_set	59	(NA) Crystal - Diabolos	N	de
737	15	filter_values_set	60	(NA) Crystal - Goblin	N	de
738	15	filter_values_set	61	(NA) Crystal - Malboro	N	de
739	15	filter_values_set	62	(NA) Crystal - Mateus	N	de
740	15	filter_values_set	63	(NA) Crystal - Zalera	N	de
741	15	filter_values_set	64	(NA) Primal - Behemoth	N	de
742	15	filter_values_set	65	(NA) Primal - Excalibur	N	de
743	15	filter_values_set	66	(NA) Primal - Exodus	N	de
744	15	filter_values_set	67	(NA) Primal - Famfrit	N	de
745	15	filter_values_set	68	(NA) Primal - Hyperion	N	de
746	15	filter_values_set	69	(NA) Primal - Lamia	N	de
747	15	filter_values_set	70	(NA) Primal - Leviathan	N	de
748	15	filter_values_set	71	(NA) Primal - Ultros	N	de
749	15	filter_values_set	72	(Oceania) Materia - Bismarck	N	de
750	15	filter_values_set	73	(Oceania) Materia - Ravana	N	de
751	15	filter_values_set	74	(Oceania) Materia - Sephirot	N	de
752	15	filter_values_set	75	(Oceania) Materia - Sophia	N	de
753	15	filter_values_set	76	(Oceania) Materia - Zurvan	N	de