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, 28339, 28324, 28328, 28327
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00715

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "372.72"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "132.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": 132,
            "rows_produced_per_join": 132,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (28294,28295,28339,28324,28328,28327)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "112.00",
              "eval_cost": "13.20",
              "prefix_cost": "125.20",
              "data_read_per_join": "6K"
            },
            "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": 132,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "102.32",
              "eval_cost": "13.20",
              "prefix_cost": "240.72",
              "data_read_per_join": "101K"
            },
            "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
None	113144	28324	0	0.000	A	A	A	A
Small	113160	28327	0	0.000	A	A	A	A
Fabric 1	113164	28328	0	0.000	A	A	A	A
Fabric 2	113165	28328	0	0.000	A	A	A	A
Fabric 3	113166	28328	0	0.000	A	A	A	A
Fabric 4	113167	28328	0	0.000	A	A	A	A
Fabric 5	113168	28328	0	0.000	A	A	A	A
Fabric 6	113169	28328	0	0.000	A	A	A	A
Fabric 7	113170	28328	0	0.000	A	A	A	A
Fabric 8	113171	28328	0	0.000	A	A	A	A
Fabric 9	113172	28328	0	0.000	A	A	A	A
Fabric 10	113173	28328	0	0.000	A	A	A	A
Fabric 11	113174	28328	0	0.000	A	A	A	A
Fabric 12	113175	28328	0	0.000	A	A	A	A
Fabric 13	113176	28328	0	0.000	A	A	A	A
Fabric 14	113177	28328	0	0.000	A	A	A	A
Fabric 15	113178	28328	0	0.000	A	A	A	A
Fabric 16	113179	28328	0	0.000	A	A	A	A
Fabric 17	113180	28328	0	0.000	A	A	A	A
Fabric 18	113181	28328	0	0.000	A	A	A	A
Fabric 19	113182	28328	0	0.000	A	A	A	A
Fabric 20	113183	28328	0	0.000	A	A	A	A
Fabric 21	113184	28328	0	0.000	A	A	A	A
Fabric 22	113185	28328	0	0.000	A	A	A	A
Fabric 23	113186	28328	0	0.000	A	A	A	A
Fabric 24	113187	28328	0	0.000	A	A	A	A
Fabric 25	113188	28328	0	0.000	A	A	A	A
Fabric 26	113189	28328	0	0.000	A	A	A	A
Fabric 27	113190	28328	0	0.000	A	A	A	A
Fabric 28	113191	28328	0	0.000	A	A	A	A
Fabric 29	113192	28328	0	0.000	A	A	A	A
Fabric 30	113193	28328	0	0.000	A	A	A	A
Fabric 31	113194	28328	0	0.000	A	A	A	A
Fabric 32	113195	28328	0	0.000	A	A	A	A
Fabric 33	113196	28328	0	0.000	A	A	A	A
Light Blue	113197	28328	0	0.000	A	A	A	A
Light Purple	113198	28328	0	0.000	A	A	A	A
Dark Purple	113199	28328	0	0.000	A	A	A	A
Red	113200	28328	0	0.000	A	A	A	A
Black	113201	28328	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
Small Suction Cup	113145	28324	1	0.500	A	A	A	A
Medium	113161	28327	1	2.000	A	A	A	A
Surprise Me!	113202	28328	1	0.000	A	A	A	A
Yes Please!	113221	28339	1	5.990	A	A	A	A
Regular Suction Cup	113146	28324	2	0.750	A	A	A	A
Large	113162	28327	2	4.000	A	A	A	A
Magnetic Hook	113147	28324	3	3.990	A	A	A	A
Extra Large	113163	28327	3	6.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