Sandbox

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 (
    17549, 34840, 4868, 20104, 10218, 25026, 
    34841, 10578
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.01504

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "142.10"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "50.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": 50,
            "rows_produced_per_join": 50,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (17549,34840,4868,20104,10218,25026,34841,10578)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "44.23",
              "eval_cost": "5.00",
              "prefix_cost": "49.23",
              "data_read_per_join": "2K"
            },
            "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": 50,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "37.88",
              "eval_cost": "5.00",
              "prefix_cost": "92.10",
              "data_read_per_join": "38K"
            },
            "used_columns": [
              "variant_id",
              "lang_code",
              "variant_name"
            ]
          }
        }
      ]
    }
  }
}

Result

variant_name	variant_id	option_id	position	modifier	modifier_type	weight_modifier_type	point_modifier_type	status
	21623	4868	0	0.000	A	A	A	A
Pale Blue	46256	10578	0	0.000	A	A	A	A
Lipstick	46257	10578	0	0.000	A	A	A	A
Turmeric	46258	10578	0	0.000	A	A	A	A
Speckled White	46259	10578	0	0.000	A	A	A	A
Speckled Latte	46260	10578	0	0.000	A	A	A	A
Sunshine Yellow	46261	10578	0	0.000	A	A	A	A
Pale Pink	46262	10578	0	0.000	A	A	A	A
Ultramarine Blue	46263	10578	0	0.000	A	A	A	A
Linen Brown	46264	10578	0	0.000	A	A	A	A
Matcha	46265	10578	0	0.000	A	A	A	A
Terracotta	46266	10578	0	0.000	A	A	A	A
Med. Lavender	46267	10578	0	0.000	A	A	A	A
Black	46268	10578	0	0.000	A	A	A	A
Grass Green	46269	10578	0	0.000	A	A	A	A
Tangerine	46270	10578	0	0.000	A	A	A	A
White	78857	20104	0	0.000	A	A	A	A
Pale Pink	78858	20104	0	0.000	A	A	A	A
Khaki	78859	20104	0	0.000	A	A	A	A
Gray Granite	78860	20104	0	0.000	A	A	A	A
Olive Green	78861	20104	0	0.000	A	A	A	A
Terracotta	78862	20104	0	0.000	A	A	A	A
Rust	78863	20104	0	0.000	A	A	A	A
Black	78864	20104	0	0.000	A	A	A	A
Terracotta	137812	34840	0	0.000	A	A	A	A
White Sand	137813	34840	0	0.000	A	A	A	A
Cobalt Blue	137814	34840	0	0.000	A	A	A	A
Black	137815	34840	0	0.000	A	A	A	A
Summer Shower	137816	34840	0	0.000	A	A	A	A
Moon Shadow	137817	34840	0	0.000	A	A	A	A
Burnished Lilac	137818	34841	0	0.000	A	A	A	A
Khaki Green	137819	34841	0	0.000	A	A	A	A
Dust Bunny	137820	34841	0	0.000	A	A	A	A
Summer Green	137821	34841	0	0.000	A	A	A	A
White Sand	137822	34841	0	0.000	A	A	A	A
Black	137823	34841	0	0.000	A	A	A	A
	21622	4868	1	4.000	A	A	A	A
Titanium Posts	44783	10218	1	0.000	A	A	A	A
Surgical Steel Posts	69169	17549	1	0.000	A	A	A	A
Pastel Sky	98494	25026	1	0.000	A	A	A	A
Non-Pierced Clip On	44784	10218	2	0.000	A	A	A	A
Titanium Posts	69170	17549	2	0.000	A	A	A	A
Sunbeam	98495	25026	2	0.000	A	A	A	A
Non-Pierced Clip On	69171	17549	3	0.000	A	A	A	A
Pale Pink	98501	25026	3	0.000	A	A	A	A
Cherry Pie	98496	25026	4	0.000	A	A	A	A
Fresh Mint	98497	25026	5	0.000	A	A	A	A
Dusty Lilac	98498	25026	6	0.000	A	A	A	A
Spring Green	98499	25026	7	0.000	A	A	A	A
Lagoon	98500	25026	8	0.000	A	A	A	A