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 (4247, 4243, 4245, 4246) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00205

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "244.87"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "87.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": 87,
            "rows_produced_per_join": 87,
            "filtered": "100.00",
            "index_condition": "((`goimagine`.`a`.`option_id` in (4247,4243,4245,4246)) and (`goimagine`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "73.58",
              "eval_cost": "8.70",
              "prefix_cost": "82.28",
              "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": 87,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "66.89",
              "eval_cost": "8.70",
              "prefix_cost": "157.87",
              "data_read_per_join": "66K"
            },
            "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
Fluorescent orange	18463	4243	1	0.000	A	A	A	A
Silver plated	18489	4245	1	0.000	A	A	A	A
14 inch	18494	4246	1	0.000	A	A	A	A
Fluorescent pink	18464	4243	2	0.000	A	A	A	A
Gold plated	18490	4245	2	0.000	A	A	A	A
16 inch	18495	4246	2	0.000	A	A	A	A
Frosted Caribbean	18465	4243	3	0.000	A	A	A	A
Gunmetal plated	18491	4245	3	0.000	A	A	A	A
18 inch	18496	4246	3	0.000	A	A	A	A
Frosted cherry blossom	18466	4243	4	0.000	A	A	A	A
Sterling silver	18492	4245	4	13.000	A	A	A	A
20 inch	18497	4246	4	0.000	A	A	A	A
Frosted citrus	18467	4243	5	0.000	A	A	A	A
14kt gold-filled	18493	4245	5	15.000	A	A	A	A
Frosted daffodil	18468	4243	6	0.000	A	A	A	A
14kt ROSE gold-filled	18499	4245	6	17.000	A	A	A	A
Frosted glacier	18469	4243	7	0.000	A	A	A	A
Frosted kiwi	18470	4243	8	0.000	A	A	A	A
Frosted Pacific	18471	4243	9	0.000	A	A	A	A
Frosted passion fruit	18472	4243	10	0.000	A	A	A	A
Frosted phantom	18473	4243	11	0.000	A	A	A	A
Frosted poppy	18474	4243	12	0.000	A	A	A	A
Frosted powder blue	18475	4243	13	0.000	A	A	A	A
Frosted raindrop	18476	4243	14	0.000	A	A	A	A
Frosted wedgewood	18477	4243	15	0.000	A	A	A	A
Frosted wisteria	18478	4243	16	0.000	A	A	A	A
Glitter black	18500	4243	17	0.000	A	A	A	A
Glitter emerald	18501	4243	18	0.000	A	A	A	A
Glitter gold	18502	4243	19	0.000	A	A	A	A
Glitter pink	18503	4243	20	0.000	A	A	A	A
Glitter purple	18504	4243	21	0.000	A	A	A	A
Glitter rainbow	18505	4243	22	0.000	A	A	A	A
Glitter red	18506	4243	23	0.000	A	A	A	A
Glitter royal blue	18507	4243	24	0.000	A	A	A	A
Glitter silver	18508	4243	25	0.000	A	A	A	A
Glitter white	18509	4243	26	0.000	A	A	A	A
Metallic bronze	18510	4243	27	0.000	A	A	A	A
Metallic silver	18513	4243	28	0.000	A	A	A	A
Mirror green	18517	4243	28	0.000	A	A	A	A
Metallic gold	18511	4243	29	0.000	A	A	A	A
Metallic rose gold	18512	4243	30	0.000	A	A	A	A
Mirror blue	18514	4243	32	0.000	A	A	A	A
Mirror bronze	18515	4243	33	0.000	A	A	A	A
Mirror gold	18516	4243	34	0.000	A	A	A	A
Mirror pink	18518	4243	35	0.000	A	A	A	A
Mirror purple	18519	4243	36	0.000	A	A	A	A
Mirror red	18520	4243	37	0.000	A	A	A	A
Mirror silver	18521	4243	38	0.000	A	A	A	A
Pearl gold	18522	4243	39	0.000	A	A	A	A
Pearl ivory	18523	4243	40	0.000	A	A	A	A
Pearl rose	18524	4243	41	0.000	A	A	A	A
Pearl silver	18525	4243	42	0.000	A	A	A	A
Pearl teal	18526	4243	43	0.000	A	A	A	A
Pearl white	18527	4243	44	0.000	A	A	A	A
Solid black	18531	4243	45	0.000	A	A	A	A
Solid brown	18532	4243	46	0.000	A	A	A	A
Solid dark blue	18533	4243	47	0.000	A	A	A	A
Solid green	18534	4243	48	0.000	A	A	A	A
Solid grey	18535	4243	49	0.000	A	A	A	A
Solid ivory	18536	4243	50	0.000	A	A	A	A
Solid light blue	18537	4243	51	0.000	A	A	A	A
Solid navy	18538	4243	52	0.000	A	A	A	A
Solid orange	18539	4243	53	0.000	A	A	A	A
Solid pink	18540	4243	54	0.000	A	A	A	A
Solid purple	18541	4243	55	0.000	A	A	A	A
Solid red	18542	4243	56	0.000	A	A	A	A
Solid turquoise	18543	4243	57	0.000	A	A	A	A
Solid white	18544	4243	58	0.000	A	A	A	A
Solid yellow	18545	4243	59	0.000	A	A	A	A
Transparent clear	18546	4243	60	0.000	A	A	A	A
Transparent dark blue	18547	4243	61	0.000	A	A	A	A
Transparent green	18548	4243	62	0.000	A	A	A	A
Transparent light blue	18549	4243	63	0.000	A	A	A	A
Transparent light grey	18550	4243	64	0.000	A	A	A	A
Transparent orange	18551	4243	65	0.000	A	A	A	A
Transparent purple	18552	4243	66	0.000	A	A	A	A
Transparent red	18553	4243	67	0.000	A	A	A	A
Transparent smoke	18554	4243	68	0.000	A	A	A	A
Transparent teal	18555	4243	69	0.000	A	A	A	A
Transparent yellow	18556	4243	70	0.000	A	A	A	A
Blue marble	18557	4243	71	0.000	A	A	A	A
Green marble	18558	4243	72	0.000	A	A	A	A
French shell	18559	4243	73	0.000	A	A	A	A
Granite	18560	4243	74	0.000	A	A	A	A
Tortoise shell	18561	4243	75	0.000	A	A	A	A
Semi-transparent white	18562	4243	76	0.000	A	A	A	A