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 (
    1098, 1100, 1099, 1101, 1102, 1103, 1104, 
    1105, 1106, 1263, 1265, 1267, 26392, 
    26393, 26388, 26391, 34067, 34065, 
    34066, 33441
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00532

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "149.12"
    },
    "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 (1098,1100,1099,1101,1102,1103,1104,1105,1106,1263,1265,1267,26392,26393,26388,26391,34067,34065,34066,33441)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "51.81",
              "eval_cost": "5.00",
              "prefix_cost": "56.81",
              "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.31",
              "eval_cost": "5.00",
              "prefix_cost": "99.12",
              "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
Weathered Copper	4778	1098	0	0.000	A	A	A	A
Brushed Aluminum	4779	1098	0	0.000	A	A	A	A
Warm Bronze	4780	1098	0	0.000	A	A	A	A
Name Only	4781	1100	0	0.000	A	A	A	A
Name & 1 Line	4782	1100	0	3.000	A	A	A	A
Name & 2 Lines	4783	1100	0	6.000	A	A	A	A
Weathered Copper	4784	1101	0	0.000	A	A	A	A
Brushed Aluminum	4785	1101	0	0.000	A	A	A	A
Warm Bronze	4786	1101	0	0.000	A	A	A	A
Small/Medium 1"	4787	1102	0	0.000	A	A	A	A
Medium/Large 1 1/8"	4788	1102	0	2.000	A	A	A	A
Name Only	4789	1103	0	0.000	A	A	A	A
Name & 1 Line	4790	1103	0	3.000	A	A	A	A
Name & 2 Lines	4791	1103	0	6.000	A	A	A	A
Weathered Copper	4792	1105	0	0.000	A	A	A	A
Brushed Alumium	4793	1105	0	0.000	A	A	A	A
Warm Bronze	4794	1105	0	0.000	A	A	A	A
Name Only	4795	1106	0	0.000	A	A	A	A
Name & 1 Line	4796	1106	0	3.000	A	A	A	A
Name & 2 Lines	4797	1106	0	6.000	A	A	A	A
Small/Medium 1"	5311	1265	0	0.000	A	A	A	A
Medium/Large 1 1/8"	5312	1265	0	2.000	A	A	A	A
Name Only	5318	1267	0	0.000	A	A	A	A
Name & 1 Line	5319	1267	0	3.000	A	A	A	A
Name & 2 Lines	5320	1267	0	6.000	A	A	A	A
One Disc	105565	26388	0	0.000	A	A	A	A
Two Discs	105578	26388	0	3.000	A	A	A	A
Three Discs	105579	26388	0	6.000	A	A	A	A
Four Discs	105580	26388	0	9.000	A	A	A	A
Five Discs	105581	26388	0	12.000	A	A	A	A
Six Discs	105582	26388	0	15.000	A	A	A	A
One	105590	26392	0	0.000	A	A	A	A
Two	105591	26392	0	3.000	A	A	A	A
Three	105592	26392	0	6.000	A	A	A	A
Four	105593	26392	0	9.000	A	A	A	A
Five	105594	26392	0	12.000	A	A	A	A
Six	105595	26392	0	15.000	A	A	A	A
As Shown	132327	33441	1	0.000	A	A	A	A
Copper	134682	34067	1	0.000	A	A	A	A
With Leather Holder	132328	33441	2	11.000	A	A	A	A
Bronze	134683	34067	2	0.000	A	A	A	A
Hole/Keychain	132329	33441	3	5.000	A	A	A	A
Aluminum	134684	34067	3	0.000	A	A	A	A