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 (28294, 28295, 28296, 28298, 28339) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00137

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "276.92"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "95.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": 95,
            "rows_produced_per_join": 95,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (28294,28295,28296,28298,28339)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "85.22",
              "eval_cost": "9.50",
              "prefix_cost": "94.72",
              "data_read_per_join": "4K"
            },
            "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": 95,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "77.69",
              "eval_cost": "9.50",
              "prefix_cost": "181.92",
              "data_read_per_join": "72K"
            },
            "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
Clear	112954	28294	0	0.000	A	A	A	A
None	112958	28295	0	0.000	A	A	A	A
No Personalization	113011	28298	0	0.000	A	A	A	A
No Thanks	113220	28339	0	0.000	A	A	A	A
Clear	112959	28295	1	0.250	A	A	A	A
White	113003	28298	1	1.000	A	A	A	A
Yes Please!	113221	28339	1	5.990	A	A	A	A
Black	113004	28298	2	1.000	A	A	A	A
Blue	113005	28298	3	1.000	A	A	A	A
Shimmer Blue	113008	28298	3	1.000	A	A	A	A
Green	113006	28298	4	1.000	A	A	A	A
Shimmer Green	113007	28298	4	1.000	A	A	A	A
Yellow	113009	28298	5	1.000	A	A	A	A
Tie-Die	113010	28298	6	1.000	A	A	A	A
Black Carbon	112928	28294	640	0.000	A	A	A	A
Carbon Black	112960	28295	640	0.250	A	A	A	A
Pumpkin Orange	112929	28294	641	0.000	A	A	A	A
Pumpkin Orange	112961	28295	641	0.250	A	A	A	A
Salmon Pink	112930	28294	642	0.000	A	A	A	A
Salmon Pink	112962	28295	642	0.250	A	A	A	A
Pink Gold	112931	28294	643	0.000	A	A	A	A
Pink Gold	112963	28295	643	0.250	A	A	A	A
Reflex Violet	112932	28294	644	0.000	A	A	A	A
Reflex Violet	112964	28295	644	0.250	A	A	A	A
Grey Lavender	112933	28294	645	0.000	A	A	A	A
Grey Lavender	112965	28295	645	0.250	A	A	A	A
Mink	112934	28294	646	0.000	A	A	A	A
Mink	112966	28295	646	0.250	A	A	A	A
Sky Blue	112935	28294	647	0.000	A	A	A	A
Sky Blue	112967	28295	647	0.250	A	A	A	A
Macro Pearl	112938	28294	650	0.000	A	A	A	A
Macro Pearl	112968	28295	650	0.250	A	A	A	A
Pearl White	112937	28294	651	0.000	A	A	A	A
Pearl White	112969	28295	651	0.250	A	A	A	A
Micro Pearl	112936	28294	652	0.000	A	A	A	A
Micro Pearl	112970	28295	652	0.250	A	A	A	A
Red Russet	112939	28294	653	0.000	A	A	A	A
Red Russet	112971	28295	653	0.250	A	A	A	A
Super Russet	112940	28294	654	0.000	A	A	A	A
Super Russet	112972	28295	654	0.250	A	A	A	A
Super Copper	112941	28294	655	0.000	A	A	A	A
Super Copper	112973	28295	655	0.250	A	A	A	A
Brilliant Gold	112942	28294	656	0.000	A	A	A	A
Brilliant Gold	112974	28295	656	0.250	A	A	A	A
Sparkle Gold	112943	28294	657	0.000	A	A	A	A
Sparkle Gold	112975	28295	657	0.250	A	A	A	A
Aztec Gold	112917	28294	658	0.000	A	A	A	A
Aztec Gold	112976	28295	658	0.250	A	A	A	A
Antique Gold	112944	28294	659	0.000	A	A	A	A
Antique Gold	112977	28295	659	0.250	A	A	A	A
Antique Bronze	112945	28294	660	0.000	A	A	A	A
Antique Bronze	112978	28295	660	0.250	A	A	A	A
Antique Copper	112918	28294	661	0.000	A	A	A	A
Antique Copper	112979	28295	661	0.250	A	A	A	A
Antique Silver	112919	28294	662	0.000	A	A	A	A
Antique Silver	112980	28295	662	0.250	A	A	A	A
Silver	112946	28294	663	0.000	A	A	A	A
Silver	112981	28295	663	0.250	A	A	A	A
Super Bronze	112947	28294	664	0.000	A	A	A	A
Super Bronze	112982	28295	664	0.250	A	A	A	A
Sunset Gold	112948	28294	665	0.000	A	A	A	A
Sunset Gold	112983	28295	665	0.250	A	A	A	A
Interference Red	112949	28294	670	0.000	A	A	A	A
Interference Red	112984	28295	670	0.250	A	A	A	A
Interference Blue	112950	28294	671	0.000	A	A	A	A
Interference Blue	112985	28295	671	0.250	A	A	A	A
Interference Green	112951	28294	672	0.000	A	A	A	A
Interference Green	112986	28295	672	0.250	A	A	A	A
Interference Violet	112952	28294	673	0.000	A	A	A	A
Interference Violet	112987	28295	673	0.250	A	A	A	A
Interference Gold	112953	28294	674	0.000	A	A	A	A
Interference Gold	112988	28295	674	0.250	A	A	A	A
Duo Red-Blue	112955	28294	680	0.000	A	A	A	A
Duo Red-Blue	112989	28295	680	0.250	A	A	A	A
Duo Blue-Green	112956	28294	681	0.000	A	A	A	A
Duo Blue-Green	112990	28295	681	0.250	A	A	A	A
Duo Green-Yellow	112957	28294	682	0.000	A	A	A	A
Duo Green-Yellow	112991	28295	682	0.250	A	A	A	A
Bright Yellow	112920	28294	683	0.000	A	A	A	A
Bright Yellow	112992	28295	683	0.250	A	A	A	A
Flamingo Pink	112921	28294	684	0.000	A	A	A	A
Flamingo Pink	112993	28295	684	0.250	A	A	A	A
Spring Green	112922	28294	685	0.000	A	A	A	A
Spring Green	112994	28295	685	0.250	A	A	A	A
Turquoise	112923	28294	686	0.000	A	A	A	A
Turquoise	112995	28295	686	0.250	A	A	A	A
True Blue	112924	28294	687	0.000	A	A	A	A
True Blue	112996	28295	687	0.250	A	A	A	A
Misty Lavender	112925	28294	688	0.000	A	A	A	A
Misty Lavender	112997	28295	688	0.250	A	A	A	A
Blue Russet	112926	28294	689	0.000	A	A	A	A
Blue Russet	112998	28295	689	0.250	A	A	A	A
Solar Gold	112927	28294	691	0.000	A	A	A	A
Solar Gold	112999	28295	691	0.250	A	A	A	A