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 (
    4483, 4485, 7825, 5968, 7347, 8151, 8208, 
    8426, 4488, 4487, 4558, 8362, 4516, 
    7494, 4423, 5751, 5752, 7159, 8192
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00973

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "284.37"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "97.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": 97,
            "rows_produced_per_join": 97,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (4483,4485,7825,5968,7347,8151,8208,8426,4488,4487,4558,8362,4516,7494,4423,5751,5752,7159,8192)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "91.69",
              "eval_cost": "9.70",
              "prefix_cost": "101.39",
              "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": 97,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "76.28",
              "eval_cost": "9.70",
              "prefix_cost": "187.37",
              "data_read_per_join": "74K"
            },
            "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
Camel 19793 4423 1 0.000 A 0.000 A 0.000 A A
Maroon 20156 4483 1 0.000 A 0.000 A 0.000 A A
Small 20158 4485 1 0.000 A 0.000 A 0.000 A A
Small 20168 4487 1 0.000 A 0.000 A 0.000 A A
Men's 20173 4488 1 0.000 A 0.000 A 0.000 A A
Right Handed 20281 4516 1 0.000 A 0.000 A 0.000 A A
Pumpkin Spice 20421 4558 1 0.000 A 0.000 A 0.000 A A
Guest Size 25783 5752 1 0.000 A 0.000 A 0.000 A A
Regular Vinyl 26863 5968 1 0.000 A 0.000 A 0.000 A A
Black 30670 7159 1 0.000 A 0.000 A 0.000 A A
Black 31356 7347 1 0.000 A 0.000 A 0.000 A A
Love Spell Type 31812 7494 1 0.000 A 0.000 A 0.000 A A
Farmhouse 32768 7825 1 0.000 A 0.000 A 0.000 A A
Single 36405 8151 1 0.000 A 0.000 A 0.000 A A
Polo Type 36580 8192 1 0.000 A 0.000 A 0.000 A A
Clothe Line Fresh 36725 8208 1 0.000 A 0.000 A 0.000 A A
Green Irish 37446 8362 1 0.000 A 0.000 A 0.000 A A
Blue 19794 4423 2 0.000 A 0.000 A 0.000 A A
Teal 20157 4483 2 0.000 A 0.000 A 0.000 A A
Medium 20159 4485 2 0.000 A 0.000 A 0.000 A A
Medium 20169 4487 2 0.000 A 0.000 A 0.000 A A
Women's 20174 4488 2 0.000 A 0.000 A 0.000 A A
Left-Handed 20327 4516 2 0.000 A 0.000 A 0.000 A A
Frosted Pumpkin 20422 4558 2 0.000 A 0.000 A 0.000 A A
Large 25784 5752 2 5.000 A 0.000 A 0.000 A A
Glitter Vinyl 26864 5968 2 0.000 A 0.000 A 0.000 A A
Green 30671 7159 2 0.000 A 0.000 A 0.000 A A
No Add On 30678 5751 2 0.000 A 0.000 A 0.000 A A
Gray 31357 7347 2 0.000 A 0.000 A 0.000 A A
Passionate Kisses 31813 7494 2 0.000 A 0.000 A 0.000 A A
Cupid's B & B 32769 7825 2 0.000 A 0.000 A 0.000 A A
Bundle of 3 36406 8151 2 9.000 A 0.000 A 0.000 A A
Sandalwood 36581 8192 2 0.000 A 0.000 A 0.000 A A
Snuggable 36726 8208 2 0.000 A 0.000 A 0.000 A A
Basil Sage Mint 37447 8362 2 0.000 A 0.000 A 0.000 A A
Green 19795 4423 3 0.000 A 0.000 A 0.000 A A
Large 20160 4485 3 0.000 A 0.000 A 0.000 A A
Large 20170 4487 3 0.000 A 0.000 A 0.000 A A
Perfect Pumpkin 20423 4558 3 0.000 A 0.000 A 0.000 A A
Soap and Scrubbie 25781 5751 3 8.000 A 0.000 A 0.000 A A
White 31358 7347 3 0.000 A 0.000 A 0.000 A A
Moonlight Way 31814 7494 3 0.000 A 0.000 A 0.000 A A
Cool Water Type 36582 8192 3 0.000 A 0.000 A 0.000 A A
Island Fresh 36727 8208 3 0.000 A 0.000 A 0.000 A A
Cannabis Flower 37448 8362 3 0.000 A 0.000 A 0.000 A A
Yellow 19796 4423 4 0.000 A 0.000 A 0.000 A A
X-Large 20161 4485 4 0.000 A 0.000 A 0.000 A A
X-Large 20171 4487 4 0.000 A 0.000 A 0.000 A A
Leaves 20424 4558 4 0.000 A 0.000 A 0.000 A A
Soap and Body Spray 25782 5751 4 6.000 A 0.000 A 0.000 A A
Dare to Be Sexy 31815 7494 4 0.000 A 0.000 A 0.000 A A
Joop Type 36583 8192 4 0.000 A 0.000 A 0.000 A A
Honey, I Washed the Kids 36728 8208 4 0.000 A 0.000 A 0.000 A A
Da Lime & Coconut 37449 8362 4 0.000 A 0.000 A 0.000 A A
Pastel Rainbow 19797 4423 5 0.000 A 0.000 A 0.000 A A
2X 20162 4485 5 2.000 A 0.000 A 0.000 A A
2X 20172 4487 5 2.000 A 0.000 A 0.000 A A
Autumn Day 20425 4558 5 0.000 A 0.000 A 0.000 A A
Gift Set (Soap, Scrubbie, Matching Spray, and 4 oz Candle) 25780 5751 5 18.000 A 0.000 A 0.000 A A
Lick Me All Over 31816 7494 5 0.000 A 0.000 A 0.000 A A
Drakkar Type 36584 8192 5 0.000 A 0.000 A 0.000 A A
Angel Wings 36729 8208 5 0.000 A 0.000 A 0.000 A A
Cucumber Melons 37450 8362 5 0.000 A 0.000 A 0.000 A A
Apple Jack & Peel 20426 4558 6 0.000 A 0.000 A 0.000 A A
Werewolf 36585 8192 6 0.000 A 0.000 A 0.000 A A
Basil Sage Mint 36730 8208 6 0.000 A 0.000 A 0.000 A A
Eucalyptus & Mint 37451 8362 6 0.000 A 0.000 A 0.000 A A
Orange Clove 20427 4558 7 0.000 A 0.000 A 0.000 A A
Fifty Shades 36586 8192 7 0.000 A 0.000 A 0.000 A A
Breezes & Sunshine 36731 8208 7 0.000 A 0.000 A 0.000 A A
Citrus Basil 37452 8362 7 0.000 A 0.000 A 0.000 A A
Candied Yams 20428 4558 8 0.000 A 0.000 A 0.000 A A
Indian Sandalwood 36587 8192 8 0.000 A 0.000 A 0.000 A A
Denim 36732 8208 8 0.000 A 0.000 A 0.000 A A
Absinthe 37453 8362 8 0.000 A 0.000 A 0.000 A A
Pumpkin Pecan Waffle 20429 4558 9 0.000 A 0.000 A 0.000 A A
Leather 36588 8192 9 0.000 A 0.000 A 0.000 A A
Sweet Orange Chili Pepper 20430 4558 10 0.000 A 0.000 A 0.000 A A
Barnwood 36589 8192 10 0.000 A 0.000 A 0.000 A A
Cinnamon Buns 20431 4558 11 0.000 A 0.000 A 0.000 A A
Dark Wood 36590 8192 11 0.000 A 0.000 A 0.000 A A
Cinnamon 20432 4558 12 0.000 A 0.000 A 0.000 A A
Winter Aspen 36591 8192 12 0.000 A 0.000 A 0.000 A A
S'mores 20433 4558 13 0.000 A 0.000 A 0.000 A A
Apple Spice 20434 4558 14 0.000 A 0.000 A 0.000 A A
Banana Nut Bread 20435 4558 15 0.000 A 0.000 A 0.000 A A
Hot Baked Apple Pie 20436 4558 16 0.000 A 0.000 A 0.000 A A
Pumpkin Spice Latte 20437 4558 17 0.000 A 0.000 A 0.000 A A
Candy Corn 20438 4558 18 0.000 A 0.000 A 0.000 A A
Acorn Harvest 20439 4558 19 0.000 A 0.000 A 0.000 A A
Autumn Magic 20440 4558 20 0.000 A 0.000 A 0.000 A A
Gingerbread 20441 4558 21 0.000 A 0.000 A 0.000 A A
Harvest moon 20442 4558 22 0.000 A 0.000 A 0.000 A A
Hillbilly Homebrew 20443 4558 23 0.000 A 0.000 A 0.000 A A
McIntosh Apple 20444 4558 24 0.000 A 0.000 A 0.000 A A
Tricks or Treats 20445 4558 25 0.000 A 0.000 A 0.000 A A