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 (
    10076, 10077, 11588, 13865, 10847, 18306, 
    11879, 9590, 9810, 9479, 9481, 9482, 
    17335, 17336, 17337, 9845, 9844, 9484, 
    15357, 15358, 9477, 25866, 19042, 9480, 
    20863, 12157, 17555
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.02353

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "219.87"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "72.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": 72,
            "rows_produced_per_join": 72,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (10076,10077,11588,13865,10847,18306,11879,9590,9810,9479,9481,9482,17335,17336,17337,9845,9844,9484,15357,15358,9477,25866,19042,9480,20863,12157,17555)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "77.93",
              "eval_cost": "7.20",
              "prefix_cost": "85.13",
              "data_read_per_join": "3K"
            },
            "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": 72,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "55.54",
              "eval_cost": "7.20",
              "prefix_cost": "147.87",
              "data_read_per_join": "55K"
            },
            "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
Red 41718 9477 1 0.000 A 0.000 A 0.000 A A
Red 41731 9479 1 0.000 A 0.000 A 0.000 A A
Opening for bag dispensing 41742 9480 1 0.000 A 0.000 A 0.000 A A
3" by 3 1/2" rectangle 41745 9481 1 0.000 A 0.000 A 0.000 A A
do not add a wristlet 41748 9482 1 0.000 A 0.000 A 0.000 A A
Square Shape 42364 9590 1 0.000 A 0.000 A 0.000 A A
Yes opening - need this bag 43190 9810 1 0.000 A 0.000 A 0.000 A A
Add a When Pigs Fly button 43322 9844 1 2.000 A 0.000 A 0.000 A A
Brown with pink pigs 43323 9845 1 0.000 A 0.000 A 0.000 A A
Black denim outside/ FTTF logo inside 44229 10076 1 0.000 A 0.000 A 0.000 A A
Gold tone hardware 44230 10077 1 0.000 A 0.000 A 0.000 A A
Black webbing 46495 10847 1 0.000 A 0.000 A 0.000 A A
No just send the bag 49455 11588 1 0.000 A 0.000 A 0.000 A A
Square Shape 52230 12157 1 0.000 A 0.000 A 0.000 A A
Navy webbing 58824 13865 1 0.000 A 0.000 A 0.000 A A
Navy webbing 61393 15357 1 0.000 A 0.000 A 0.000 A A
Silver tone hardware and Navy zipper 61395 15358 1 0.000 A 0.000 A 0.000 A A
Holds 32 Oz tumbler 68030 17335 1 0.000 A 0.000 A 0.000 A A
Silver tone gnome and hardware red zipper 68032 17336 1 0.000 A 0.000 A 0.000 A A
Slip Pocket 68035 17337 1 0.000 A 0.000 A 0.000 A A
Black webbing 69179 17555 1 0.000 A 0.000 A 0.000 A A
Regular size (32oz) 71947 18306 1 0.000 A 0.000 A 0.000 A A
Square Shape 74884 19042 1 0.000 A 0.000 A 0.000 A A
Phone pocket 81915 20863 1 0.000 A 0.000 A 0.000 A A
Orange 41719 9477 2 0.000 A 0.000 A 0.000 A A
Orange 41732 9479 2 0.000 A 0.000 A 0.000 A A
no opening / use as small purse 41743 9480 2 0.000 A 0.000 A 0.000 A A
4" square 41744 9481 2 0.000 A 0.000 A 0.000 A A
Please add a wristlet 41747 9482 2 3.000 A 0.000 A 0.000 A A
Bone Shape 42365 9590 2 2.000 A 0.000 A 0.000 A A
No embroidered opening use for treats 43188 9810 2 0.000 A 0.000 A 0.000 A A
Make with a pink button 43321 9844 2 0.000 A 0.000 A 0.000 A A
green with pink pigs 43324 9845 2 0.000 A 0.000 A 0.000 A A
FTTF Logo on White fabric 44228 10076 2 0.000 A 0.000 A 0.000 A A
Silver tone hardware 44231 10077 2 0.000 A 0.000 A 0.000 A A
Matching Fabric 46496 10847 2 5.000 A 0.000 A 0.000 A A
Please send this cup 49454 11588 2 28.000 A 0.000 A 0.000 A A
Bone Shape 52229 12157 2 2.000 A 0.000 A 0.000 A A
Denim over webbing 58825 13865 2 8.000 A 0.000 A 0.000 A A
Matching Fabric over Webbing 61394 15357 2 6.000 A 0.000 A 0.000 A A
Gunmetal tone Hardware and gears zipper / pulls 61396 15358 2 5.500 A 0.000 A 0.000 A A
Holds 64 oz tumbler 68031 17335 2 10.000 A 0.000 A 0.000 A A
Rosegold tone gnome and Hardware - black zipper 68033 17336 2 5.000 A 0.000 A 0.000 A A
Phone / Pen pocket 68036 17337 2 5.000 A 0.000 A 0.000 A A
Matching Fabric over Webbing 69180 17555 2 5.000 A 0.000 A 0.000 A A
Large size (64 oz) 71948 18306 2 10.000 A 0.000 A 0.000 A A
Bone Shape 74885 19042 2 2.000 A 0.000 A 0.000 A A
Card slot pocket 81916 20863 2 5.000 A 0.000 A 0.000 A A
Pink 41720 9477 3 0.000 A 0.000 A 0.000 A A
Yellow 41733 9479 3 0.000 A 0.000 A 0.000 A A
3 1/2 " by 5" bone shape 41746 9481 3 2.000 A 0.000 A 0.000 A A
Rainbow gnome and hardware - beige zipper 68034 17336 3 7.000 A 0.000 A 0.000 A A
Phone / Pen / Cards Pocket 68037 17337 3 10.000 A 0.000 A 0.000 A A
Yellow 41721 9477 4 0.000 A 0.000 A 0.000 A A
Pink 41734 9479 4 0.000 A 0.000 A 0.000 A A
Green 41722 9477 5 0.000 A 0.000 A 0.000 A A
Green (Aqua) 41735 9479 5 0.000 A 0.000 A 0.000 A A
Blue 41723 9477 6 0.000 A 0.000 A 0.000 A A
Blue 41736 9479 6 0.000 A 0.000 A 0.000 A A
Tan 41724 9477 7 0.000 A 0.000 A 0.000 A A
Tan 41737 9479 7 0.000 A 0.000 A 0.000 A A
Brown 41725 9477 8 0.000 A 0.000 A 0.000 A A
Brown 41738 9479 8 0.000 A 0.000 A 0.000 A A
Purple 41728 9477 9 0.000 A 0.000 A 0.000 A A
Purple 41741 9479 9 0.000 A 0.000 A 0.000 A A
Gray 41727 9477 10 0.000 A 0.000 A 0.000 A A
Gray 41740 9479 10 0.000 A 0.000 A 0.000 A A
Black 41726 9477 11 0.000 A 0.000 A 0.000 A A
Black 41739 9479 11 0.000 A 0.000 A 0.000 A A