SELECT 
  cscart_product_feature_variants.variant_id, 
  cscart_product_feature_variants.position, 
  cscart_product_feature_variants.color, 
  cscart_product_feature_variant_descriptions.variant 
FROM 
  cscart_product_feature_variants 
  LEFT JOIN cscart_product_feature_variant_descriptions ON cscart_product_feature_variants.variant_id = cscart_product_feature_variant_descriptions.variant_id 
  AND cscart_product_feature_variant_descriptions.lang_code = 'en' 
WHERE 
  cscart_product_feature_variants.variant_id IN (
    10340, 
    10878, 
    12308, 
    12338, 
    12350, 
    12357, 
    13914, 
    13918, 
    13928, 
    13933, 
    13936, 
    10368, 
    14310, 
    10964, 
    12218, 
    12340, 
    12341, 
    12727, 
    12734, 
    12768, 
    13217, 
    13222, 
    14312, 
    14313, 
    14314, 
    14315, 
    14316, 
    14317, 
    13917, 
    13929, 
    13934, 
    13937, 
    13964, 
    9851, 
    10942, 
    10372, 
    9870, 
    10949, 
    12221, 
    12354, 
    12718, 
    12736, 
    13220, 
    13234, 
    13235, 
    13257, 
    14357, 
    14012, 
    10371, 
    11856, 
    11029, 
    12219, 
    12305, 
    12352, 
    12728, 
    13921, 
    10330, 
    10990, 
    12307, 
    12346, 
    12356, 
    12735, 
    12767, 
    12771, 
    13210, 
    13212, 
    13225, 
    13228, 
    13267, 
    14514, 
    14515, 
    13919, 
    13935, 
    10370, 
    9876, 
    10953, 
    14518, 
    13999, 
    9981, 
    10900, 
    10379, 
    11107, 
    10391, 
    13915, 
    10390, 
    10482, 
    11686, 
    12342, 
    12985, 
    12988, 
    14373, 
    13367, 
    14374, 
    14375, 
    14376, 
    14377, 
    14378, 
    14379, 
    14380, 
    10375, 
    12315, 
    12358, 
    12770, 
    12983, 
    14388, 
    14389, 
    14390, 
    14391, 
    14392, 
    14393, 
    14394, 
    14395, 
    14396, 
    14397, 
    14398, 
    14399, 
    14400, 
    14401, 
    14402, 
    14403, 
    13922, 
    14322, 
    12007, 
    12989, 
    13370, 
    13371, 
    14227, 
    13368, 
    10583, 
    10603, 
    10870, 
    11972, 
    10818, 
    14244, 
    14499, 
    10897, 
    14502, 
    10670, 
    10877, 
    13034, 
    10836, 
    10830, 
    12345, 
    13029, 
    13372, 
    13385, 
    14264, 
    10787, 
    12347, 
    13373, 
    10671, 
    10852, 
    10892, 
    10668, 
    13036, 
    14441, 
    12987, 
    13390, 
    14454, 
    10786, 
    14381, 
    14407, 
    13396, 
    14412, 
    14419, 
    11878, 
    12351, 
    14420, 
    13028, 
    13040, 
    10705, 
    10598, 
    13037, 
    13410, 
    10885, 
    14449, 
    12353, 
    14453, 
    13397, 
    14492, 
    10732, 
    10986, 
    12769, 
    13112, 
    13121, 
    13463, 
    13471, 
    13479, 
    14043, 
    14195, 
    10584, 
    14491
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00473

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "691.21"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_product_feature_variants",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 192,
            "rows_produced_per_join": 192,
            "filtered": "100.00",
            "index_condition": "(`danishecarter_latest`.`cscart_product_feature_variants`.`variant_id` in (10340,10878,12308,12338,12350,12357,13914,13918,13928,13933,13936,10368,14310,10964,12218,12340,12341,12727,12734,12768,13217,13222,14312,14313,14314,14315,14316,14317,13917,13929,13934,13937,13964,9851,10942,10372,9870,10949,12221,12354,12718,12736,13220,13234,13235,13257,14357,14012,10371,11856,11029,12219,12305,12352,12728,13921,10330,10990,12307,12346,12356,12735,12767,12771,13210,13212,13225,13228,13267,14514,14515,13919,13935,10370,9876,10953,14518,13999,9981,10900,10379,11107,10391,13915,10390,10482,11686,12342,12985,12988,14373,13367,14374,14375,14376,14377,14378,14379,14380,10375,12315,12358,12770,12983,14388,14389,14390,14391,14392,14393,14394,14395,14396,14397,14398,14399,14400,14401,14402,14403,13922,14322,12007,12989,13370,13371,14227,13368,10583,10603,10870,11972,10818,14244,14499,10897,14502,10670,10877,13034,10836,10830,12345,13029,13372,13385,14264,10787,12347,13373,10671,10852,10892,10668,13036,14441,12987,13390,14454,10786,14381,14407,13396,14412,14419,11878,12351,14420,13028,13040,10705,10598,13037,13410,10885,14449,12353,14453,13397,14492,10732,10986,12769,13112,13121,13463,13471,13479,14043,14195,10584,14491))",
            "cost_info": {
              "read_cost": "422.41",
              "eval_cost": "38.40",
              "prefix_cost": "460.81",
              "data_read_per_join": "507K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_product_feature_variant_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "danishecarter_latest.cscart_product_feature_variants.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 192,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "192.00",
              "eval_cost": "38.40",
              "prefix_cost": "691.21",
              "data_read_per_join": "580K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

variant_id position color variant
12987 0 #ffffff 1
12221 0 #ffffff 1 (None)
12988 0 #ffffff 2
12989 0 #ffffff 3
14373 0 3
12219 0 #ffffff 3 (Yellow)
12771 0 #ffffff 360° camera
14420 0 4
12218 0 #ffffff 4 (Green)
13034 0 #ffffff 4x2
12983 0 #ffffff 5
9851 0 #ffffff 595
13036 0 #ffffff 6x2
13037 0 #ffffff 6x4
12985 0 #ffffff 7
13040 0 #ffffff 8x6
13928 0 #ffffff A/C (man.)
10942 0 #ffffff ABARTH
13372 0 #ffffff ABS
13396 0 #ffffff ABS
13368 0 #ffffff ABS
14314 0 abs
13390 0 #ffffff ABS
13463 0 #ffffff ABS
13367 0 #ffffff ABS
12358 0 #ffffff Adaptive Cruise Control
11107 0 #ffffff AHLMANN
13370 0 #ffffff Air Suspension
10949 0 #ffffff AIXAM
12356 0 #ffffff All wheel drive
10953 0 #ffffff ALPINA
10964 0 #ffffff ASTON MARTIN
12340 0 #ffffff Automatic
13929 0 #ffffff Automatic air conditioning
14515 0 automaticRainSensor
14403 0 automaticRainSensor
14264 0 auxiliaryHeating
14412 0 auxiliaryHeating
14377 0 auxiliaryHeating
14402 0 awning
9876 0 #ffffff B3
13964 0 #ffffff Belgium
12341 0 #ffffff Black
12727 0 #ffffff Black
12347 0 #ffffff Blue
10584 0 #ffffff Box
14227 0 BoxSemiTrailer
14492 0 BoxTruck
10368 0 #ffffff Cabriolet / Roadster
12769 0 #ffffff Camera
13210 0 #ffffff car play
10705 0 #ffffff Cement mixer
14491 0 centralLocking
14316 0 centralLocking
14375 0 centralLocking
14502 0 ChassisSemiTrailer
14453 0 ChassisTruck
10986 0 #ffffff CITROEN
12736 0 #ffffff Cloth
10340 0 #ffffff Coaches
10379 0 #ffffff Combined Dredger Loader
10390 0 #ffffff Compactor
9870 0 #ffffff Cross
12357 0 #ffffff Cruise control
10990 0 #ffffff DACIA
10877 0 #ffffff DAF
14310 0 DB9
10583 0 #ffffff Deep-freeze transporter
11686 0 #ffffff DETHLEFFS
13918 0 #ffffff Diesel
13371 0 #ffffff Disk Brake
10878 0 #ffffff DROEGMOELLER
10330 0 #ffffff Duster
13921 0 #ffffff Electric
13220 0 #ffffff Electric Exterior Mirrors
13222 0 #ffffff Electric Heated Seats
13217 0 #ffffff Electric windows
14390 0 electricHeatedSeats
14313 0 esp
13385 0 #ffffff ESP
14389 0 esp
13410 0 #ffffff ESP
13471 0 #ffffff ESP
14012 0 #ffffff EU Edition
12305 0 #ffffff Euro3
12307 0 #ffffff Euro5
12308 0 #ffffff Euro6
13914 0 #ffffff Fixed
10786 0 #ffffff FLIEGL
10787 0 #ffffff FORD
14379 0 frenchBed
12767 0 #ffffff Front
13228 0 #ffffff Front fog lights
12354 0 #ffffff Front wheel drive
12734 0 #ffffff Full leather
13999 0 #ffffff Germany
11878 0 #ffffff GINAF
12346 0 #ffffff Green
12342 0 #ffffff Grey
12728 0 #ffffff Grey
13121 0 #ffffff H1
13225 0 #ffffff Hands Free Phone System
10668 0 #ffffff Hazardous load
13212 0 #ffffff Heated steering wheel
14397 0 highBeamAssist
14043 0 #ffffff Hungary
13922 0 #ffffff Hybrid (petrol/electric)
10885 0 #ffffff IVECO
11972 0 #ffffff KEL-BERG
11029 0 #ffffff KIA
10818 0 #ffffff KRONE
13234 0 #ffffff Leather steering wheel
14395 0 leatherSteeringWheel
13235 0 #ffffff Light sensor
13028 0 #ffffff Local traffic
13029 0 #ffffff Long distance traffic
13919 0 #ffffff LPG
10830 0 #ffffff MAN
12338 0 #ffffff Manual gearbox
10836 0 #ffffff MERCEDES-BENZ
14401 0 middleSeatingArrangement
14357 0 multifunctionalWheel
14400 0 multifunctionalWheel
13373 0 #ffffff Navigation System
13397 0 #ffffff Navigation System
14399 0 navigationPreparation
14312 0 navigationSystem
14388 0 navigationSystem
13934 0 #ffffff New vehicle
13936 0 #ffffff No
13915 0 #ffffff On request
12351 0 #ffffff Orange
10870 0 #ffffff Other
10375 0 #ffffff Other
10603 0 #ffffff Other semi-trailers
9981 0 #ffffff Other substructures
10732 0 #ffffff Other vans/trucks up to 7.5 t
12735 0 #ffffff Part leather
14376 0 particulateFilterDiesel
14518 0 particulateFilterDiesel
13917 0 #ffffff Petrol
13479 0 #ffffff Power Assisted Steering
14315 0 powerAssistedSteering
14374 0 powerAssistedSteering
14195 0 #ffffff Pre-Registration
12768 0 #ffffff Rear
14391 0 rearGarage
12345 0 #ffffff Red
14322 0 RefrigeratorBodySemiTrailer
14407 0 RefrigeratorBodyTruck
10852 0 #ffffff RENAULT
14514 0 roadworthy
10370 0 #ffffff Saloon
10892 0 #ffffff SCANIA
12007 0 #ffffff SCHMITZ
12770 0 #ffffff Self-steering systems
14392 0 sepShower
13112 0 #ffffff short
12352 0 #ffffff Silver
12718 0 #ffffff Sliding door right
10372 0 #ffffff Small Car
14393 0 solarEnergySystem
11856 0 #ffffff Sportage
14244 0 StakeBodyAndTarpaulinSemiTrailer
14441 0 StakeBodyAndTarpaulinTrailer
14381 0 StakeBodyAndTarpaulinTruck
10670 0 #ffffff Standard tractor/trailer unit
10371 0 #ffffff SUV/Off-road Vehicle/Pickup Truck
14499 0 SwapChassisSemiTrailer
14454 0 SwapChassisTrailer
14449 0 SwapChassisTruck
10598 0 #ffffff Timber carrier
14419 0 TipperTruck
13257 0 #ffffff Tire pressure monitoring
14396 0 tirePressureMonitoring
12315 0 #ffffff Trailer coupling fix
14394 0 tv
13933 0 #ffffff Used vehicle
10482 0 #ffffff Van
10897 0 #ffffff VANHOOL
10671 0 #ffffff Volume trailer
10900 0 #ffffff VOLVO
14317 0 warranty
14380 0 warranty
14378 0 wc
10391 0 #ffffff Wheeled loader
12350 0 #ffffff White
13267 0 #ffffff Wifi Hotspot
14398 0 wifiHotspot
12353 0 #ffffff Yellow
13935 0 #ffffff Yes
13937 0 #ffffff Yes