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 (
    16005, 22255, 18426, 22256, 18427, 15968, 
    18424, 22261, 25815, 22736, 22739, 
    18425
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.01340

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "426.90"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "148.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": 148,
            "rows_produced_per_join": 148,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (16005,22255,18426,22256,18427,15968,18424,22261,25815,22736,22739,18425)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "131.80",
              "eval_cost": "14.80",
              "prefix_cost": "146.60",
              "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": 148,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "117.51",
              "eval_cost": "14.80",
              "prefix_cost": "278.90",
              "data_read_per_join": "113K"
            },
            "used_columns": [
              "variant_id",
              "lang_code",
              "variant_name"
            ]
          }
        }
      ]
    }
  }
}

Result

variant_name	variant_id	option_id	position	modifier_type	weight_modifier_type	point_modifier_type	status
Brown Faux Fur	61969	15968	0	A	A	A	A
Black Faux Fur	61970	15968	0	A	A	A	A
White	61973	16005	1	A	A	A	A
Arctic Blue	72429	18424	1	A	A	A	A
Brown Pom	72434	18425	1	A	A	A	A
White	72436	18426	1	A	A	A	A
Black Pom	72439	18427	1	A	A	A	A
Brown	87834	22255	1	A	A	A	A
Brown	87872	22256	1	A	A	A	A
Brown	87959	22261	1	A	A	A	A
Small (Infant)	89620	22736	1	A	A	A	A
Small (Infant)	89629	22739	1	A	A	A	A
SHHH Coffee	103111	25815	1	A	A	A	A
Linen	61974	16005	2	A	A	A	A
Sea Blue	72430	18424	2	A	A	A	A
Black Pom	72435	18425	2	A	A	A	A
Linen	72437	18426	2	A	A	A	A
Brown Pom	72440	18427	2	A	A	A	A
Black	87835	22255	2	A	A	A	A
Black	87873	22256	2	A	A	A	A
Black	87960	22261	2	A	A	A	A
Medium (Toddler)	89621	22736	2	A	A	A	A
Medium (Toddler)	89630	22739	2	A	A	A	A
Fisherman	61975	16005	3	A	A	A	A
Medium Blue	72431	18424	3	A	A	A	A
Fisherman	72438	18426	3	A	A	A	A
Silver Fox	87836	22255	3	A	A	A	A
Silver Fox	87874	22256	3	A	A	A	A
Silver Fox	87961	22261	3	A	A	A	A
Silver Fox	89445	18425	3	A	A	A	A
Large (3-10 years)	89622	22736	3	A	A	A	A
Large (3-10 years)	89631	22739	3	A	A	A	A
Coffee,Teach,Repeat	135257	25815	3	A	A	A	A
Royal Blue	72432	18424	4	A	A	A	A
Oatmeal	87807	16005	4	A	A	A	A
Dark Gray	87837	22255	4	A	A	A	A
Oatmeal	87845	18426	4	A	A	A	A
Dark Gray	87875	22256	4	A	A	A	A
Dark Gray	87962	22261	4	A	A	A	A
Dark Gray	89446	18425	4	A	A	A	A
Navy Blue	72433	18424	5	A	A	A	A
Taupe	87808	16005	5	A	A	A	A
Platinum Gray	87838	22255	5	A	A	A	A
Taupe	87846	18426	5	A	A	A	A
Platinum Gray	87876	22256	5	A	A	A	A
Platinum Gray	87963	22261	5	A	A	A	A
Platinum Gray	89447	18425	5	A	A	A	A
Coffee & Cats	135259	25815	5	A	A	A	A
Chocolate	87809	16005	6	A	A	A	A
White	87839	22255	6	A	A	A	A
Chocolate	87847	18426	6	A	A	A	A
White	87877	22256	6	A	A	A	A
Dark Denim	87935	18424	6	A	A	A	A
White	87964	22261	6	A	A	A	A
White	89448	18425	6	A	A	A	A
Murder Shows and ...	135260	25815	6	A	A	A	A
Barley	87810	16005	7	A	A	A	A
Grape	87840	22255	7	A	A	A	A
Barley	87848	18426	7	A	A	A	A
Grape	87878	22256	7	A	A	A	A
Navy Tweed	87936	18424	7	A	A	A	A
Grape	87965	22261	7	A	A	A	A
Grape	89449	18425	7	A	A	A	A
Good Vibes Only	135261	25815	7	A	A	A	A
Pale Gray	87811	16005	8	A	A	A	A
Lavender	87841	22255	8	A	A	A	A
Pale Gray	87849	18426	8	A	A	A	A
Lavender	87879	22256	8	A	A	A	A
Blueberry Buckle	87937	18424	8	A	A	A	A
Lavender	87966	22261	8	A	A	A	A
Lavender	89450	18425	8	A	A	A	A
Silver Gray	87812	16005	9	A	A	A	A
Kelly Green	87842	22255	9	A	A	A	A
Silver Gray	87850	18426	9	A	A	A	A
Kelly Green	87880	22256	9	A	A	A	A
Antique Teal	87938	18424	9	A	A	A	A
Kelly Green	87967	22261	9	A	A	A	A
Kelly Green	89451	18425	9	A	A	A	A
Charcoal Gray	87813	16005	10	A	A	A	A
Pink	87843	22255	10	A	A	A	A
Charcoal Gray	87851	18426	10	A	A	A	A
Pink	87881	22256	10	A	A	A	A
Turquoise	87939	18424	10	A	A	A	A
Pink	87968	22261	10	A	A	A	A
Pink	89452	18425	10	A	A	A	A
Dark Gray Heather	87814	16005	11	A	A	A	A
Blush Pink	87844	22255	11	A	A	A	A
Dark Gray Heather	87852	18426	11	A	A	A	A
Blush Pink	87882	22256	11	A	A	A	A
Jelly Bean	87940	18424	11	A	A	A	A
Blush Pink	87969	22261	11	A	A	A	A
Blush Pink	89453	18425	11	A	A	A	A
Black	87815	16005	12	A	A	A	A
Black	87853	18426	12	A	A	A	A
Yellow	87941	18424	12	A	A	A	A
Dusty Blue	87816	16005	13	A	A	A	A
Dusty Blue	87854	18426	13	A	A	A	A
Soft Pink	87942	18424	13	A	A	A	A
Colonial Blue	87817	16005	14	A	A	A	A
Colonial Blue	87855	18426	14	A	A	A	A
Pink	87943	18424	14	A	A	A	A
Midnight Blue	87818	16005	15	A	A	A	A
Midnight Blue	87856	18426	15	A	A	A	A
Dusty Pink	87944	18424	15	A	A	A	A
Navy	87819	16005	16	A	A	A	A
Navy	87857	18426	16	A	A	A	A
Cranberry	87945	18424	16	A	A	A	A
Orion Blue	87820	16005	17	A	A	A	A
Orion Blue	87858	18426	17	A	A	A	A
Maroon	87946	18424	17	A	A	A	A
Sage	87821	16005	18	A	A	A	A
Sage	87859	18426	18	A	A	A	A
Red	87947	18424	18	A	A	A	A
Dusty Green	87822	16005	19	A	A	A	A
Dusty Green	87860	18426	19	A	A	A	A
Fire Red	87948	18424	19	A	A	A	A
Olive	87823	16005	20	A	A	A	A
Olive	87861	18426	20	A	A	A	A
Orange	87949	18424	20	A	A	A	A
Mustard	87824	16005	21	A	A	A	A
Mustard	87862	18426	21	A	A	A	A
Lavender	87950	18424	21	A	A	A	A
Honey	87825	16005	22	A	A	A	A
Honey	87863	18426	22	A	A	A	A
Amethyst	87951	18424	22	A	A	A	A
Blush Pink	87826	16005	23	A	A	A	A
Blush Pink	87864	18426	23	A	A	A	A
Grape	87952	18424	23	A	A	A	A
Rose Shocking	87827	16005	24	A	A	A	A
Rose Shocking	87865	18426	24	A	A	A	A
Dark Purple	87953	18424	24	A	A	A	A
Burgundy	87828	16005	25	A	A	A	A
Burgundy	87866	18426	25	A	A	A	A
White	87954	18424	25	A	A	A	A
Cranberry	87829	16005	26	A	A	A	A
Cranberry	87867	18426	26	A	A	A	A
Ivory	87955	18424	26	A	A	A	A
Rust	87830	16005	27	A	A	A	A
Rust	87868	18426	27	A	A	A	A
Gray Mist	87956	18424	27	A	A	A	A
Terracotta	87831	16005	28	A	A	A	A
Terracotta	87869	18426	28	A	A	A	A
Silver Gray	87957	18424	28	A	A	A	A
Dusty Purple	87832	16005	29	A	A	A	A
Dusty Purple	87870	18426	29	A	A	A	A
Black	87958	18424	29	A	A	A	A
Purple	87833	16005	30	A	A	A	A
Purple	87871	18426	30	A	A	A	A