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 hint 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