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 (
    4104, 1125, 877, 347, 9128, 3801, 1218, 
    3705, 7680, 5623, 5622, 3711, 3813, 
    3814, 3706, 3713, 3714, 22119, 5574, 
    5575, 6756, 319
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.01187

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "317.37"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "110.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": 110,
            "rows_produced_per_join": 110,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (4104,1125,877,347,9128,3801,1218,3705,7680,5623,5622,3711,3813,3814,3706,3713,3714,22119,5574,5575,6756,319)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "101.07",
              "eval_cost": "11.00",
              "prefix_cost": "112.07",
              "data_read_per_join": "5K"
            },
            "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": 110,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "84.30",
              "eval_cost": "11.00",
              "prefix_cost": "207.37",
              "data_read_per_join": "84K"
            },
            "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
Without Sanitizer 1760 319 0 0.000 A 0.000 A 0.000 A A
Teal 3436 347 0 0.000 A 0.000 A 0.000 A A
Bright Lime Green 3437 347 0 0.000 A 0.000 A 0.000 A A
Dark Pink 3438 347 0 0.000 A 0.000 A 0.000 A A
Pink 3439 347 0 0.000 A 0.000 A 0.000 A A
Light Pink 3440 347 0 0.000 A 0.000 A 0.000 A A
Mediteranian Blue 3441 347 0 0.000 A 0.000 A 0.000 A A
Black 3453 347 0 0.000 A 0.000 A 0.000 A A
Small holds 1 oz 3843 877 0 0.000 A 0.000 A 0.000 A A
Tall holds 2 oz 3844 877 0 0.000 A 0.000 A 0.000 A A
Purple 12542 347 0 0.000 A 0.000 A 0.000 A A
No Gift Wrapped 15917 3705 0 0.000 A 0.000 A 0.000 A A
No Gift Wrapped 15924 3706 0 0.000 A 0.000 A 0.000 A A
No Monogram 15940 3711 0 0.000 A 0.000 A 0.000 A A
Mom 15952 3713 0 0.000 A 0.000 A 0.000 A A
Wife 15953 3713 0 0.000 A 0.000 A 0.000 A A
Split Ring 15954 3714 0 0.000 A 0.000 A 0.000 A A
Swivel Clasp 15955 3714 0 0.000 A 0.000 A 0.000 A A
Black 16242 3801 0 0.000 A 0.000 A 0.000 A A
Light Blue 16243 3801 0 0.000 A 0.000 A 0.000 A A
Aqua 16244 3801 0 0.000 A 0.000 A 0.000 A A
Med Blue 16245 3801 0 0.000 A 0.000 A 0.000 A A
Navy 16246 3801 0 0.000 A 0.000 A 0.000 A A
Pink 16247 3801 0 0.000 A 0.000 A 0.000 A A
Light Pink 16248 3801 0 0.000 A 0.000 A 0.000 A A
Dark Pink 16249 3801 0 0.000 A 0.000 A 0.000 A A
Red 16250 3801 0 0.000 A 0.000 A 0.000 A A
Tan 16251 3801 0 0.000 A 0.000 A 0.000 A A
Teal 16252 3801 0 0.000 A 0.000 A 0.000 A A
Yellow 16253 3801 0 0.000 A 0.000 A 0.000 A A
Green 16254 3801 0 0.000 A 0.000 A 0.000 A A
Neon Green 16255 3801 0 0.000 A 0.000 A 0.000 A A
Purple 16256 3801 0 0.000 A 0.000 A 0.000 A A
White 16265 3813 0 0.000 A 0.000 A 0.000 A A
No Embroidered Name 16268 3814 0 0.000 A 0.000 A 0.000 A A
Ivory Kona Cotton 24773 5574 0 0.000 A 0.000 A 0.000 A A
White Kona Cotton 24774 5574 0 0.000 A 0.000 A 0.000 A A
Pink / White 25011 5622 0 0.000 A 0.000 A 0.000 A A
Light Blue / White 25012 5622 0 0.000 A 0.000 A 0.000 A A
Pink / Ivory 25013 5622 0 0.000 A 0.000 A 0.000 A A
Light Blue / Ivory 25014 5622 0 0.000 A 0.000 A 0.000 A A
Cream 87369 22119 0 0.000 A 0.000 A 0.000 A A
Brown Burlap 87370 22119 0 0.000 A 0.000 A 0.000 A A
with 1 oz Sanitizer 1761 319 1 2.500 A 0.500 A 0.000 A A
Glitter White 1925 347 1 0.000 A 0.000 A 0.000 A A
w/out Sanitizer 5185 1218 1 0.000 A 0.000 A 0.000 A A
Birthday gift Wrap 15918 3705 1 2.000 A 0.125 A 0.000 A A
Christmas Gift Wrap 15925 3706 1 5.000 A 0.200 A 0.000 A A
Black 15942 3711 1 5.000 A 0.000 A 0.000 A A
Neon Green 16266 3813 1 0.000 A 0.000 A 0.000 A A
Add Name to Embroidered Flip Flops 16269 3814 1 10.000 A 0.000 A 0.000 A A
Gingerbread Boy - Plain 30414 6756 1 0.000 A 0.000 A 0.000 A A
Light Green 40400 9128 1 0.000 A 0.000 A 0.000 A A
White 1926 347 2 0.000 A 0.000 A 0.000 A A
with Sanitizer 1 oz 5186 1218 2 2.500 A 0.060 A 0.000 A A
Wedding Gift Wrap 15919 3705 2 2.000 A 0.125 A 0.000 A A
Birthday Gift Wrap 15926 3706 2 5.000 A 0.200 A 0.000 A A
Blue 15943 3711 2 5.000 A 0.000 A 0.000 A A
Neon Pink 16267 3813 2 0.000 A 0.000 A 0.000 A A
with 2 oz Sanitizer 20330 319 2 2.500 A 0.500 A 0.000 A A
Gingerbread Girl - Plain 30415 6756 2 0.000 A 0.000 A 0.000 A A
Green 40401 9128 2 0.000 A 0.000 A 0.000 A A
Anniversary Gift Wrap 15920 3705 3 2.000 A 0.125 A 0.000 A A
Wedding Gift Wrap 15927 3706 3 5.000 A 0.200 A 0.000 A A
Brown 15941 3711 3 5.000 A 0.000 A 0.000 A A
Gingerbread Boy Appliqué 30416 6756 3 2.000 A 0.000 A 0.000 A A
Dark Green 40402 9128 3 0.000 A 0.000 A 0.000 A A
Christmas Gift Wrap 15921 3705 4 2.000 A 0.125 A 0.000 A A
Gold 15946 3711 4 5.000 A 0.000 A 0.000 A A
Baby Pink 17683 3706 4 5.000 A 0.200 A 0.000 A A
Gingerbread Girl - Appliqué 30417 6756 4 2.000 A 0.000 A 0.000 A A
Yellow 40393 9128 4 0.000 A 0.000 A 0.000 A A
Baby Pink Gift Wrap 15922 3705 5 2.000 A 0.125 A 0.000 A A
Olive 15944 3711 5 5.000 A 0.000 A 0.000 A A
Baby Blue 17684 3706 5 5.000 A 0.200 A 0.000 A A
Light Pink 40395 9128 5 0.000 A 0.000 A 0.000 A A
Baby Blue Gift Warp 15923 3705 6 2.000 A 0.125 A 0.000 A A
Red 15945 3711 6 5.000 A 0.000 A 0.000 A A
Baby Shower 17685 3706 6 5.000 A 0.200 A 0.000 A A
Pink 40396 9128 6 0.000 A 0.000 A 0.000 A A
Silver 15947 3711 7 5.000 A 0.000 A 0.000 A A
White 17686 3706 7 5.000 A 0.000 A 0.000 A A
Dark Pink 40397 9128 7 0.000 A 0.000 A 0.000 A A
Red 17687 3706 8 5.000 A 0.200 A 0.000 A A
White 40398 9128 8 0.000 A 0.000 A 0.000 A A
Black 40399 9128 9 0.000 A 0.000 A 0.000 A A
Pink Stripe 17774 4104 10 0.000 A 0.000 A 0.000 A A
8”x8” 24775 5575 10 0.000 A 0.000 A 0.000 A A
Aqua 32590 7680 10 0.000 A 0.000 A 0.000 A A
Light Purple 40404 9128 10 0.000 A 0.000 A 0.000 A A
Purple 40403 9128 11 0.000 A 0.000 A 0.000 A A
Dark Purple 40405 9128 12 0.000 A 0.000 A 0.000 A A
Light Blue 40406 9128 13 0.000 A 0.000 A 0.000 A A
Blue 40407 9128 14 0.000 A 0.000 A 0.000 A A
Dark Blue 40408 9128 15 0.000 A 0.000 A 0.000 A A
Navy 40409 9128 16 0.000 A 0.000 A 0.000 A A
Red 40394 9128 17 0.000 A 0.000 A 0.000 A A
Pink Floral 17775 4104 20 0.000 A 0.000 A 0.000 A A
9”x9” 24776 5575 20 0.000 A 0.000 A 0.000 A A
Lime Green 32589 7680 20 0.000 A 0.000 A 0.000 A A
Blue Stripe 17776 4104 30 0.000 A 0.000 A 0.000 A A
10”x10” 24777 5575 30 0.000 A 0.000 A 0.000 A A
Pink 32588 7680 30 0.000 A 0.000 A 0.000 A A
Blue Floral 17777 4104 40 0.000 A 0.000 A 0.000 A A
11”x11” 24778 5575 40 1.000 A 0.000 A 0.000 A A
White 32587 7680 40 0.000 A 0.000 A 0.000 A A
12”x12” 24779 5575 50 1.000 A 0.000 A 0.000 A A
Yellow 32591 7680 50 0.000 A 0.000 A 0.000 A A