SELECT 
  b.variant_name, 
  a.* 
FROM 
  product_option_variants AS a 
  LEFT JOIN product_option_variants_descriptions AS b ON b.variant_id = a.variant_id 
  AND b.lang_code = 'en' 
WHERE 
  1 
  AND a.option_id IN (
    1867, 1979, 1978, 1977, 2260, 2259, 1866, 
    2508, 2015, 2014, 2507, 1976, 1996
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00149

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "289.88"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "100.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "a",
            "access_type": "range",
            "possible_keys": [
              "status",
              "option_id",
              "option_id_2"
            ],
            "key": "option_id",
            "used_key_parts": [
              "option_id",
              "status"
            ],
            "key_length": "6",
            "rows_examined_per_scan": 100,
            "rows_produced_per_join": 100,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (1867,1979,1978,1977,2260,2259,1866,2508,2015,2014,2507,1976,1996)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "90.73",
              "eval_cost": "10.00",
              "prefix_cost": "100.73",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "variant_id",
              "option_id",
              "position",
              "modifier",
              "modifier_type",
              "weight_modifier",
              "weight_modifier_type",
              "point_modifier",
              "point_modifier_type",
              "status",
              "etsy_variant_id"
            ]
          }
        },
        {
          "table": {
            "table_name": "b",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "goimagine.a.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 100,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "79.15",
              "eval_cost": "10.00",
              "prefix_cost": "189.88",
              "data_read_per_join": "76K"
            },
            "used_columns": [
              "variant_id",
              "lang_code",
              "variant_name"
            ]
          }
        }
      ]
    }
  }
}

Result

variant_name variant_id option_id position modifier modifier_type weight_modifier weight_modifier_type point_modifier point_modifier_type status etsy_variant_id
3 Years 7848 1866 0 0.000 A 0.000 A 0.000 A A
4 Years 7849 1866 0 0.000 A 0.000 A 0.000 A A
5 Years 7850 1866 0 0.000 A 0.000 A 0.000 A A
6 Years 7851 1866 0 0.000 A 0.000 A 0.000 A A
3-6 months 7852 1867 0 0.000 A 0.000 A 0.000 A A
6-12 months 7853 1867 0 0.000 A 0.000 A 0.000 A A
12-18 months 7854 1867 0 0.000 A 0.000 A 0.000 A A
18-24 months 7855 1867 0 0.000 A 0.000 A 0.000 A A
2T 7856 1867 0 1.000 A 0.000 A 0.000 A A
3T 7865 1867 0 1.000 A 0.000 A 0.000 A A
4T 7866 1867 0 1.000 A 0.000 A 0.000 A A
5T 7867 1867 0 1.000 A 0.000 A 0.000 A A
Adult Small 7868 1867 0 2.000 A 0.000 A 0.000 A A
Adult Medium 7869 1867 0 2.000 A 0.000 A 0.000 A A
Adult Large 7870 1867 0 2.000 A 0.000 A 0.000 A A
Adult Extra Large 7871 1867 0 2.000 A 0.000 A 0.000 A A
Adult 2XL 7872 1867 0 4.000 A 0.000 A 0.000 A A
Adult 3XL 7873 1867 0 5.000 A 0.000 A 0.000 A A
Adult 4XL 7874 1867 0 6.000 A 0.000 A 0.000 A A
XS 8363 1976 0 0.000 A 0.000 A 0.000 A A
S 8364 1976 0 0.000 A 0.000 A 0.000 A A
M 8365 1976 0 0.000 A 0.000 A 0.000 A A
L 8366 1976 0 0.000 A 0.000 A 0.000 A A
XL 8367 1976 0 0.000 A 0.000 A 0.000 A A
2XL 8368 1976 0 2.000 A 0.000 A 0.000 A A
3XL 8369 1976 0 3.000 A 0.000 A 0.000 A A
4XL 8370 1976 0 4.000 A 0.000 A 0.000 A A
3-6 months bodysuit 8371 1977 0 1.000 A 0.000 A 0.000 A A
3-6 months t-shirt 8372 1977 0 0.000 A 0.000 A 0.000 A A
6-12 months bodysuit 8373 1977 0 1.000 A 0.000 A 0.000 A A
6-12 months t-shirt 8374 1977 0 0.000 A 0.000 A 0.000 A A
12-18 months bodysuit 8375 1977 0 1.000 A 0.000 A 0.000 A A
12-18 months t-shirt 8376 1977 0 0.000 A 0.000 A 0.000 A A
18-24 months bodysuit 8377 1977 0 1.000 A 0.000 A 0.000 A A
18-24 months t-shirt 8378 1977 0 0.000 A 0.000 A 0.000 A A
2T 8379 1977 0 1.000 A 0.000 A 0.000 A A
3T 8380 1977 0 1.000 A 0.000 A 0.000 A A
4T 8381 1977 0 1.000 A 0.000 A 0.000 A A
5T 8382 1977 0 1.000 A 0.000 A 0.000 A A
Youth S (6-8) 8383 1977 0 2.000 A 0.000 A 0.000 A A
Youth M (10-12) 8384 1977 0 2.000 A 0.000 A 0.000 A A
Youth L (12-14) 8385 1977 0 2.000 A 0.000 A 0.000 A A
Youth XL (16-18) 8386 1977 0 2.000 A 0.000 A 0.000 A A
Adult XS 8387 1977 0 2.000 A 0.000 A 0.000 A A
Adult S 8388 1977 0 2.000 A 0.000 A 0.000 A A
Adult M 8389 1977 0 2.000 A 0.000 A 0.000 A A
Adult L 8390 1977 0 2.000 A 0.000 A 0.000 A A
Adult XL 8391 1977 0 2.000 A 0.000 A 0.000 A A
Adult 2XL 8392 1977 0 3.000 A 0.000 A 0.000 A A
Adult 3XL 8393 1977 0 4.000 A 0.000 A 0.000 A A
Adult 4XL 8394 1977 0 5.000 A 0.000 A 0.000 A A
Lion (male) 8395 1978 0 0.000 A 0.000 A 0.000 A A
Lioness (female) 8396 1978 0 0.000 A 0.000 A 0.000 A A
Cub 8397 1978 0 0.000 A 0.000 A 0.000 A A
Black 8398 1979 0 0.000 A 0.000 A 0.000 A A
White 8399 1979 0 0.000 A 0.000 A 0.000 A A
Gray 8400 1979 0 0.000 A 0.000 A 0.000 A A
Gray 8507 1996 0 0.000 A 0.000 A 0.000 A A
White 8508 1996 0 0.000 A 0.000 A 0.000 A A
S 8559 2014 0 0.000 A 0.000 A 0.000 A A
M 8560 2014 0 0.000 A 0.000 A 0.000 A A
L 8561 2014 0 0.000 A 0.000 A 0.000 A A
XL 8562 2014 0 0.000 A 0.000 A 0.000 A A
2XL 8563 2014 0 2.500 A 0.000 A 0.000 A A
3XL 8564 2014 0 4.000 A 0.000 A 0.000 A A
White 8565 2015 0 0.000 A 0.000 A 0.000 A A
Black 8566 2015 0 0.000 A 0.000 A 0.000 A A
Coral 8567 2015 0 0.000 A 0.000 A 0.000 A A
Aqua Blue 8568 2015 0 0.000 A 0.000 A 0.000 A A
Red 8569 2015 0 0.000 A 0.000 A 0.000 A A
Raising Mayhem 10047 2259 0 0.000 A 0.000 A 0.000 A A
Mini Mayhem 10048 2259 0 0.000 A 0.000 A 0.000 A A
2T Baseball 10049 2260 0 0.000 A 0.000 A 0.000 A A
3T Baseball 10050 2260 0 0.000 A 0.000 A 0.000 A A
4T Baseball 10051 2260 0 0.000 A 0.000 A 0.000 A A
5T Baseball 10052 2260 0 0.000 A 0.000 A 0.000 A A
Youth S Baseball 10053 2260 0 0.000 A 0.000 A 0.000 A A
Youth M Baseball 10054 2260 0 0.000 A 0.000 A 0.000 A A
Youth L Baseball 10055 2260 0 0.000 A 0.000 A 0.000 A A
Adult XS Baseball 10056 2260 0 0.000 A 0.000 A 0.000 A A
Adult S Baseball 10057 2260 0 3.000 A 0.000 A 0.000 A A
Ladies S Football 10058 2260 0 5.000 A 0.000 A 0.000 A A
Adult M Baseball 10059 2260 0 3.000 A 0.000 A 0.000 A A
Ladies L Football 10060 2260 0 5.000 A 0.000 A 0.000 A A
Adult L Baseball 10061 2260 0 3.000 A 0.000 A 0.000 A A
Adult XL Baseball 10062 2260 0 3.000 A 0.000 A 0.000 A A
Ladies XL Football 10063 2260 0 5.000 A 0.000 A 0.000 A A
Adult 2XL Baseball 10064 2260 0 5.000 A 0.000 A 0.000 A A
Ladies 2XL Football 10065 2260 0 6.000 A 0.000 A 0.000 A A
Ladies Plus 1: 14-16 Football 10066 2260 0 8.000 A 0.000 A 0.000 A A
Ladies Plus 2: 18-20 Football 10067 2260 0 8.000 A 0.000 A 0.000 A A
Ladies Plus 3: 22-24 Football 10068 2260 0 8.000 A 0.000 A 0.000 A A
Ladies Plus 4: 26-28 Football 10069 2260 0 8.000 A 0.000 A 0.000 A A
You Are My Sonshine 10893 2507 0 0.000 A 0.000 A 0.000 A A
Sonshine 10894 2507 0 0.000 A 0.000 A 0.000 A A
You are My Sonshine 10895 2508 0 0.000 A 0.000 A 0.000 A A
Her Only Sonshine 10896 2508 0 0.000 A 0.000 A 0.000 A A
8 Years 20564 1866 0 0.000 A 0.000 A 0.000 A A
10 Years 20565 1866 0 0.000 A 0.000 A 0.000 A A
5XL 25635 1976 0 6.000 A 0.000 A 0.000 A A