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 (
    23843, 23842, 9531, 9534, 9533, 9532
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00342

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "131.29"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "45.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": 45,
            "rows_produced_per_join": 45,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (23843,23842,9531,9534,9533,9532)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "41.33",
              "eval_cost": "4.50",
              "prefix_cost": "45.83",
              "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": 45,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "35.95",
              "eval_cost": "4.50",
              "prefix_cost": "86.29",
              "data_read_per_join": "34K"
            },
            "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
DIY No Paint	42037	9531	0	0.000	A	A	A	A
A one layer	42069	9532	0	0.000	A	A	A	A
Maple	71429	9533	0	0.000	A	A	A	A
Girl	93934	23842	0	0.000	A	A	A	A
Boy	93935	23842	0	0.000	A	A	A	A
One Layer	93936	23843	0	0.000	A	A	A	A
Two Layer	93937	23843	0	2.000	A	A	A	A
Engraved Details No Seal	42038	9531	1	0.500	A	A	A	A
B one layer	42070	9532	1	0.000	A	A	A	A
Walnut	42071	9533	1	1.000	A	A	A	A
A1. Black	42039	9531	2	1.000	A	A	A	A
C one layer	71430	9532	2	0.000	A	A	A	A
A2. Tan	42040	9531	3	1.000	A	A	A	A
D two layers	71431	9532	3	2.000	A	A	A	A
A3. Grey	42041	9531	4	1.000	A	A	A	A
E two Layers	71432	9532	4	2.000	A	A	A	A
A4. White	42042	9531	5	1.000	A	A	A	A
F two Layers	71433	9532	5	2.000	A	A	A	A
A5. Pink	42043	9531	6	1.000	A	A	A	A
B1. Red	42044	9531	7	1.000	A	A	A	A
B2. Orange	42045	9531	8	1.000	A	A	A	A
B3. Pumpkin	42046	9531	9	1.000	A	A	A	A
B4. Cream	42047	9531	10	1.000	A	A	A	A
B5. Yellow	42048	9531	11	1.000	A	A	A	A
C1. Mint	42049	9531	12	1.000	A	A	A	A
C2. Green	42050	9531	13	1.000	A	A	A	A
C3. Sky Blue	42051	9531	14	1.000	A	A	A	A
C4. Royal Blue	42052	9531	15	1.000	A	A	A	A
C5. Purple	42053	9531	16	1.000	A	A	A	A
D1. Pastel Purple	42054	9531	17	1.000	A	A	A	A
D2. Pastel Blue	42055	9531	18	1.000	A	A	A	A
D3. Pastel Green	42056	9531	19	1.000	A	A	A	A
D4. Pastel Yellow	42057	9531	20	1.000	A	A	A	A
D5. Pastel Orange	42058	9531	21	1.000	A	A	A	A
E1. Pastel Red	42059	9531	22	1.000	A	A	A	A
E2. Pastel Pink	42060	9531	23	1.000	A	A	A	A
E3. Wine	42061	9531	24	1.000	A	A	A	A
E4. Rose Red	42062	9531	25	1.000	A	A	A	A
E5. Navy Blue	42063	9531	26	1.000	A	A	A	A
F1. Dark Brown	42064	9531	27	1.000	A	A	A	A
F2. Olive Green	42065	9531	28	1.000	A	A	A	A
F3. Metallic Gold	42066	9531	29	1.000	A	A	A	A
F4. Metallic Silver	42067	9531	30	1.000	A	A	A	A
F5. Sealed	42068	9531	31	1.000	A	A	A	A