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 (
    36353, 36358, 36349, 36348, 36350, 36351, 
    36352, 36354, 36344, 36355
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00172

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "369.03"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "128.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": 128,
            "rows_produced_per_join": 128,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (36353,36358,36349,36348,36350,36351,36352,36354,36344,36355)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "114.76",
              "eval_cost": "12.80",
              "prefix_cost": "127.56",
              "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": 128,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "100.66",
              "eval_cost": "12.80",
              "prefix_cost": "241.03",
              "data_read_per_join": "98K"
            },
            "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
Black	143327	36344	0	0.000	A	A	A	A
White	143328	36344	0	0.000	A	A	A	A
Silver	143329	36344	0	0.000	A	A	A	A
Yellow	143330	36344	0	0.000	A	A	A	A
Red	143331	36344	0	0.000	A	A	A	A
Pink	143332	36344	0	0.000	A	A	A	A
Lavender	143333	36344	0	0.000	A	A	A	A
Mint	143334	36344	0	0.000	A	A	A	A
Rose Gold	143335	36344	0	0.000	A	A	A	A
Gold	143336	36344	0	0.000	A	A	A	A
Navy Blue	143337	36344	0	0.000	A	A	A	A
Purple	143338	36344	0	0.000	A	A	A	A
Teal	143339	36344	0	0.000	A	A	A	A
Light Blue	143340	36344	0	0.000	A	A	A	A
Orange	143341	36344	0	0.000	A	A	A	A
Burgundy	143342	36344	0	0.000	A	A	A	A
Black	143391	36349	0	0.000	A	A	A	A
White	143392	36349	0	0.000	A	A	A	A
Mint	143393	36349	0	0.000	A	A	A	A
Apple Green	143394	36349	0	0.000	A	A	A	A
Neon Green	143395	36349	0	0.000	A	A	A	A
Bordeau	143396	36349	0	0.000	A	A	A	A
Dark Cardinal Red	143397	36349	0	0.000	A	A	A	A
Orange	143398	36349	0	0.000	A	A	A	A
Violet	143399	36349	0	0.000	A	A	A	A
Lime	143400	36349	0	0.000	A	A	A	A
Military Green	143401	36349	0	0.000	A	A	A	A
Dark Green	143402	36349	0	0.000	A	A	A	A
Hot Coral	143403	36349	0	0.000	A	A	A	A
Red	143404	36349	0	0.000	A	A	A	A
Baby Pink	143405	36349	0	0.000	A	A	A	A
Purple	143406	36349	0	0.000	A	A	A	A
Gray	143407	36349	0	0.000	A	A	A	A
Camel Tan	143408	36349	0	0.000	A	A	A	A
Flame Red	143409	36349	0	0.000	A	A	A	A
Fuchsia	143410	36349	0	0.000	A	A	A	A
Neon Pink	143411	36349	0	0.000	A	A	A	A
Silver	143412	36349	0	0.000	A	A	A	A
Brown	143413	36349	0	0.000	A	A	A	A
Cream	143414	36349	0	0.000	A	A	A	A
Seafoam	143415	36349	0	0.000	A	A	A	A
Aqua	143416	36349	0	0.000	A	A	A	A
Teal	143417	36349	0	0.000	A	A	A	A
Gold	143418	36349	0	0.000	A	A	A	A
Rose Gold	143419	36349	0	0.000	A	A	A	A
Medium Yellow	143420	36349	0	0.000	A	A	A	A
Pale Blue	143421	36349	0	0.000	A	A	A	A
Royal Blue	143422	36349	0	0.000	A	A	A	A
Beige	143423	36349	0	0.000	A	A	A	A
Black Glitter	143424	36350	0	0.000	A	A	A	A
White Glitter	143425	36350	0	0.000	A	A	A	A
Baby Blue Giltter	143426	36350	0	0.000	A	A	A	A
Aqua Glitter	143427	36350	0	0.000	A	A	A	A
Neon Green Glitter	143428	36350	0	0.000	A	A	A	A
Jade Glitter	143429	36350	0	0.000	A	A	A	A
Gray Glitter	143430	36350	0	0.000	A	A	A	A
Neon Yellow Glitter	143431	36350	0	0.000	A	A	A	A
Lght Pink Glitter	143432	36350	0	0.000	A	A	A	A
Cherry Glitter	143433	36350	0	0.000	A	A	A	A
Lavender Glitter	143434	36350	0	0.000	A	A	A	A
Light Copper Glitter	143435	36350	0	0.000	A	A	A	A
Multi-Color Glitter	143436	36350	0	0.000	A	A	A	A
Fuchsia	143437	36351	0	0.000	A	A	A	A
Pink	143438	36351	0	0.000	A	A	A	A
Gold	143439	36351	0	0.000	A	A	A	A
Green	143440	36351	0	0.000	A	A	A	A
Sky Blue	143441	36351	0	0.000	A	A	A	A
Silver	143442	36351	0	0.000	A	A	A	A
Rainbow	143443	36351	0	0.000	A	A	A	A
Fuchsia	143444	36352	0	0.000	A	A	A	A
Pink	143445	36352	0	0.000	A	A	A	A
Orange	143446	36352	0	0.000	A	A	A	A
Gold	143447	36352	0	0.000	A	A	A	A
Sky Blue	143448	36352	0	0.000	A	A	A	A
Aqua / Teal	143449	36352	0	0.000	A	A	A	A
Royal Blue	143450	36352	0	0.000	A	A	A	A
Black	143472	36355	0	0.000	A	A	A	A
Coral	143473	36355	0	0.000	A	A	A	A
Gray	143474	36355	0	0.000	A	A	A	A
Powder Pink	143475	36355	0	0.000	A	A	A	A
Powder Blue	143476	36355	0	0.000	A	A	A	A
Rose Gold	143477	36355	0	0.000	A	A	A	A
Silver	143478	36355	0	0.000	A	A	A	A
Swamp Green	143479	36355	0	0.000	A	A	A	A
White	143480	36355	0	0.000	A	A	A	A
Gray	143484	36358	0	0.000	A	A	A	A
Navy Blue	143485	36358	0	0.000	A	A	A	A
White / Black Plaid	143486	36358	0	0.000	A	A	A	A
Red / Black Plaid	143487	36358	0	0.000	A	A	A	A
Mauve Plaid	143488	36358	0	0.000	A	A	A	A
Mint Green	143489	36358	0	0.000	A	A	A	A
Red / Green Plaid	143490	36358	0	0.000	A	A	A	A
Pink Tie Dye	143491	36358	0	0.000	A	A	A	A
3" ( For Koozies)	143378	36348	1	0.000	A	A	A	A
4"	143451	36354	1	0.000	A	A	A	A
4" ( 0-6 months)	143379	36348	2	0.500	A	A	A	A
5"	143452	36354	2	1.000	A	A	A	A
5" ( 6-12 months)	143380	36348	3	1.000	A	A	A	A
6"	143453	36354	3	2.000	A	A	A	A
5.5" ( 18 mo - 3T)	143381	36348	4	1.500	A	A	A	A
7"	143454	36354	4	3.000	A	A	A	A
6" ( 4T-5T)	143382	36348	5	2.000	A	A	A	A
8"	143455	36354	5	4.000	A	A	A	A
6.6" ( Youth XS)	143385	36348	6	2.500	A	A	A	A
9"	143456	36354	6	5.000	A	A	A	A
7" ( Youth S)	143383	36348	7	3.000	A	A	A	A
10"	143457	36354	7	6.000	A	A	A	A
7.5" ( Youth M)	143384	36348	8	3.500	A	A	A	A
11"	143458	36354	8	7.000	A	A	A	A
8" (Youth L)	143386	36348	9	4.000	A	A	A	A
12"	143459	36354	9	8.000	A	A	A	A
9" ( Woman's Fitted)	143387	36348	10	4.500	A	A	A	A
13"	143460	36354	10	9.000	A	A	A	A
10" ( Adult S-L)	143388	36348	11	5.000	A	A	A	A
14"	143461	36354	11	10.000	A	A	A	A
11" ( Adult XL-3X)	143389	36348	12	5.500	A	A	A	A
15"	143462	36354	12	11.000	A	A	A	A
11.5" ( Adult 4x)	143390	36348	13	6.000	A	A	A	A
16"	143463	36354	13	12.000	A	A	A	A
17"	143464	36354	14	13.000	A	A	A	A
18"	143465	36354	15	14.000	A	A	A	A
19"	143466	36354	16	15.000	A	A	A	A
20"	143467	36354	17	16.000	A	A	A	A
21"	143468	36354	18	17.000	A	A	A	A
22"	143469	36354	19	18.000	A	A	A	A
23"	143470	36354	20	19.000	A	A	A	A
24"	143471	36354	21	20.000	A	A	A	A