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 (
    35623, 35624, 29032, 29031, 29030, 29029, 
    28906, 28905, 28904, 28903, 29056, 
    29055, 29054, 29053, 27934, 27933, 
    27932, 29148, 29147, 29146, 29145, 
    28097, 28096, 28095, 35585, 28939, 
    28938, 29040, 29039, 29038, 29037, 
    28702, 28701, 28700, 28699, 27928, 
    27927, 27926
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.01845

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "425.81"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "141.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": 141,
            "rows_produced_per_join": 141,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (35623,35624,29032,29031,29030,29029,28906,28905,28904,28903,29056,29055,29054,29053,27934,27933,27932,29148,29147,29146,29145,28097,28096,28095,35585,28939,28938,29040,29039,29038,29037,28702,28701,28700,28699,27928,27927,27926)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "146.08",
              "eval_cost": "14.10",
              "prefix_cost": "160.18",
              "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": 141,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "110.53",
              "eval_cost": "14.10",
              "prefix_cost": "284.81",
              "data_read_per_join": "107K"
            },
            "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
AS SHOWN - YOUR INFO	111815	27927	1	0.000	A	A	A	A
Facebook	111818	27928	1	0.000	A	A	A	A
BLANK	111831	27933	1	0.000	A	A	A	A
Facebook	111834	27934	1	0.000	A	A	A	A
AS SHOWN - YOUR INFO	112278	28096	1	0.000	A	A	A	A
Facebook	112281	28097	1	0.000	A	A	A	A
AS SHOWN - YOUR INFO	114442	28700	1	0.000	A	A	A	A
Black/Silver	114445	28701	1	0.000	A	A	A	A
Facebook	114451	28702	1	0.000	A	A	A	A
AS SHOWN- 20 OZ	115289	28904	1	0.000	A	A	A	A
Red	115295	28905	1	0.000	A	A	A	A
Facebook	115311	28906	1	0.000	A	A	A	A
SINGLE SIDED- AS SHOWN	115462	28938	1	0.000	A	A	A	A
Facebook	115468	28939	1	0.000	A	A	A	A
AS SHOWN	115795	29030	1	0.000	A	A	A	A
Natural	115798	29031	1	0.000	A	A	A	A
Facebook	115801	29032	1	0.000	A	A	A	A
Name Only	115817	29038	1	0.000	A	A	A	A
Natural	115821	29039	1	0.000	A	A	A	A
Facebook	115824	29040	1	0.000	A	A	A	A
AS SHOWN	115862	29054	1	0.000	A	A	A	A
Natural	115865	29055	1	0.000	A	A	A	A
Facebook	115868	29056	1	0.000	A	A	A	A
AS SHOWN	116143	29146	1	0.000	A	A	A	A
Natural	116146	29147	1	0.000	A	A	A	A
Facebook	116149	29148	1	0.000	A	A	A	A
SINGLE SIDED	140495	35585	1	0.000	A	A	A	A
SINGLE SIDED	140687	35623	1	0.000	A	A	A	A
SINGLE SIDED- AS SHOWN- NO PROOF	140689	35624	1	0.000	A	A	A	A
ARTIST CHOICE - NO PROOF	111816	27927	2	3.000	A	A	A	A
GoImagine	111819	27928	2	0.000	A	A	A	A
AS SHOWN- YOUR INFO	111832	27933	2	12.000	A	A	A	A
GoImagine	111835	27934	2	0.000	A	A	A	A
ARTIST CHOICE - NO PROOF	112279	28096	2	3.000	A	A	A	A
GoImagine	112282	28097	2	0.000	A	A	A	A
ARTIST CHOICE - NO PROOF	114443	28700	2	2.000	A	A	A	A
Black/Gold	114446	28701	2	0.000	A	A	A	A
GoImagine	114452	28702	2	0.000	A	A	A	A
ARTIST CHOICE- 20 OZ	115290	28904	2	4.000	A	A	A	A
Blue	115296	28905	2	0.000	A	A	A	A
GoImagine	115312	28906	2	0.000	A	A	A	A
SINGLE SIDED- ARTIST CHOICE	115463	28938	2	3.200	A	A	A	A
GoImagine	115469	28939	2	0.000	A	A	A	A
ARTIST CHOICE	115796	29030	2	5.000	A	A	A	A
Black	115799	29031	2	0.000	A	A	A	A
GoImagine	115802	29032	2	0.000	A	A	A	A
Name/Title	115818	29038	2	1.590	A	A	A	A
Black	115822	29039	2	0.000	A	A	A	A
GoImagine	115825	29040	2	0.000	A	A	A	A
ARTIST CHOICE	115863	29054	2	5.000	A	A	A	A
Black	115866	29055	2	0.000	A	A	A	A
GoImagine	115869	29056	2	0.000	A	A	A	A
ARTIST CHOICE	116144	29146	2	5.000	A	A	A	A
Black	116147	29147	2	0.000	A	A	A	A
GoImagine	116150	29148	2	0.000	A	A	A	A
DOUBLE SIDED	140496	35585	2	3.200	A	A	A	A
DOUBLE SIDED	140688	35623	2	2.190	A	A	A	A
SINGLE- ARTIST CHOICE- NO PROOF	140690	35624	2	2.190	A	A	A	A
CUSTOM DESIGN- PROOF	111817	27927	3	6.000	A	A	A	A
Pintrest	111820	27928	3	0.000	A	A	A	A
ARTIST CHOICE- NO PROOF	111833	27933	3	16.000	A	A	A	A
Pintrest	111836	27934	3	0.000	A	A	A	A
CUSTOM DESIGN- PROOF	112280	28096	3	7.000	A	A	A	A
Pintrest	112283	28097	3	0.000	A	A	A	A
CUSTOM DESIGN- PROOF	114444	28700	3	6.000	A	A	A	A
L.Brown/Black	114447	28701	3	0.000	A	A	A	A
Pintrest	114453	28702	3	0.000	A	A	A	A
CUSTOM DESIGN- 20 OZ	115291	28904	3	8.000	A	A	A	A
Pink	115297	28905	3	0.000	A	A	A	A
Pintrest	115313	28906	3	0.000	A	A	A	A
SINGLE SIDED- CUSTOM DESIGN	115464	28938	3	6.400	A	A	A	A
Pintrest	115470	28939	3	0.000	A	A	A	A
CUSTOM DESIGN	115797	29030	3	10.000	A	A	A	A
White	115800	29031	3	0.000	A	A	A	A
Pintrest	115803	29032	3	0.000	A	A	A	A
Name/Date	115819	29038	3	1.850	A	A	A	A
White	115823	29039	3	0.000	A	A	A	A
Pintrest	115826	29040	3	0.000	A	A	A	A
CUSTOM DESIGN	115864	29054	3	10.000	A	A	A	A
White	115867	29055	3	0.000	A	A	A	A
Pintrest	115870	29056	3	0.000	A	A	A	A
CUSTOM DESIGN	116145	29146	3	10.000	A	A	A	A
White	116148	29147	3	0.000	A	A	A	A
Pintrest	116151	29148	3	0.000	A	A	A	A
SINGLE SIDED- CUSTOM DESIGN- PROOF	140691	35624	3	3.790	A	A	A	A
Etsy	111821	27928	4	0.000	A	A	A	A
Etsy	111837	27934	4	0.000	A	A	A	A
Etsy	112284	28097	4	0.000	A	A	A	A
D.Brown/Black	114448	28701	4	0.000	A	A	A	A
Etsy	114454	28702	4	0.000	A	A	A	A
AS SHOWN- 30 OZ	115292	28904	4	1.600	A	A	A	A
Teal	115298	28905	4	0.000	A	A	A	A
Etsy	115314	28906	4	0.000	A	A	A	A
DOUBLE SIDED- AS SHOWN	115465	28938	4	0.000	A	A	A	A
Etsy	115471	28939	4	0.000	A	A	A	A
Etsy	115804	29032	4	0.000	A	A	A	A
Name/Date/Title	115820	29038	4	2.100	A	A	A	A
Etsy	115827	29040	4	0.000	A	A	A	A
Etsy	115871	29056	4	0.000	A	A	A	A
Etsy	116152	29148	4	0.000	A	A	A	A
DOUBLE- AS SHOWN- NO PROOF	140692	35624	4	0.000	A	A	A	A
CUSTOM DESIGN- PROOF	141885	27933	4	20.000	A	A	A	A
Other	111822	27928	5	0.000	A	A	A	A
Other	111838	27934	5	0.000	A	A	A	A
Other	112285	28097	5	0.000	A	A	A	A
Gray/Black	114449	28701	5	0.000	A	A	A	A
Other	114455	28702	5	0.000	A	A	A	A
ARTIST CHOICE- 30 OZ	115293	28904	5	5.600	A	A	A	A
L. Blue	115299	28905	5	0.000	A	A	A	A
Other	115315	28906	5	0.000	A	A	A	A
DOUBLE SIDED- ARTIST CHOICE	115466	28938	5	3.200	A	A	A	A
Other	115472	28939	5	0.000	A	A	A	A
Other	115805	29032	5	0.000	A	A	A	A
Other	115828	29040	5	0.000	A	A	A	A
Other	115872	29056	5	0.000	A	A	A	A
Other	116153	29148	5	0.000	A	A	A	A
DOUBLE SIDED- ARTIST CHOICE- NO PROOF	140693	35624	5	1.600	A	A	A	A
Rawhide/Black	114450	28701	6	0.000	A	A	A	A
CUSTOM DESIGN- 30 OZ	115294	28904	6	9.600	A	A	A	A
L. Purple	115300	28905	6	0.000	A	A	A	A
DOUBLE SIDED- CUSTOM DESIGN	115467	28938	6	6.400	A	A	A	A
DOUBLE SIDED- CUSTOM DESIGN- PROOF	140694	35624	6	3.200	A	A	A	A
Purple	115301	28905	7	0.000	A	A	A	A
Grey	115302	28905	8	0.000	A	A	A	A
Navy	115303	28905	9	0.000	A	A	A	A
Orange	115304	28905	10	0.000	A	A	A	A
Maroon	115305	28905	11	0.000	A	A	A	A
White	115306	28905	12	0.000	A	A	A	A
Green	115307	28905	13	0.000	A	A	A	A
Black	115308	28905	14	0.000	A	A	A	A
Coral	115309	28905	15	0.000	A	A	A	A
Yellow	115310	28905	16	0.000	A	A	A	A