Sandbox

SELECT 
  b.variant_name, 
  a.* 
FROM 
  cscart_product_option_variants AS a 
  LEFT JOIN cscart_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 (622, 623, 624) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00133

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "67.36"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "37.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": 37,
            "rows_produced_per_join": 37,
            "filtered": "100.00",
            "index_condition": "((`cscartdb`.`a`.`option_id` in (622,623,624)) and (`cscartdb`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "13.71",
              "eval_cost": "3.70",
              "prefix_cost": "17.41",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "variant_id",
              "option_id",
              "position",
              "modifier",
              "modifier_type",
              "weight_modifier",
              "weight_modifier_type",
              "point_modifier",
              "point_modifier_type",
              "status",
              "note_required"
            ]
          }
        },
        {
          "table": {
            "table_name": "b",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "cscartdb.a.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 37,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "9.25",
              "eval_cost": "3.70",
              "prefix_cost": "30.36",
              "data_read_per_join": "28K"
            },
            "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	note_required
No / Stock base design	14119	624	1	0.000	A	A	A	A
Bubblegum Pink - Pearlescent - TMT original	14512	622	1	0.000	A	A	A	A
No marble, one color only	16362	623	1	0.000	A	A	A	A
Yes (add fox tail loop)	14120	624	2	10.000	A	A	A	A
Caribbean - Pearlescent	14513	622	2	0.000	A	A	A	A
Yes marble. 2 colors only. Add colors in product notes below	16363	623	2	15.000	A	A	A	A	1
Cobalt Madness - Pearlescent	14514	622	3	0.000	A	A	A	A
Dark Night - Pearlescent	14515	622	4	0.000	A	A	A	A
Dusty Rose - Pearlescent - TMT original	14516	622	5	0.000	A	A	A	A
Forest Green - Pearlescent	14517	622	6	0.000	A	A	A	A
Galaxy Purple - Pearlescent - TMT original	14518	622	7	0.000	A	A	A	A
Granny Smith - Pearlescent	14519	622	8	0.000	A	A	A	A
Intense Purple - Pearlescent	14520	622	9	0.000	A	A	A	A
Lemon Peel - Pearlescent	14521	622	10	0.000	A	A	A	A
Lilac Shimmer - Pearlescent	14522	622	11	0.000	A	A	A	A
Magenta - Pearlescent	14523	622	12	0.000	A	A	A	A
Pearly Gates - Pearlescent - TMT original	14524	622	13	0.000	A	A	A	A
Pink Gold - Pearlescent	14525	622	14	0.000	A	A	A	A
Pumpkin orange - Pearlescent	14526	622	15	0.000	A	A	A	A
Red Shimmer - Pearlescent	14527	622	16	0.000	A	A	A	A
Rich Gold - Pearlescent	14528	622	17	0.000	A	A	A	A
Shiny Penny - Pearlescent	14529	622	18	0.000	A	A	A	A
Silver - Pearlescent	14530	622	19	0.000	A	A	A	A
Silver Amethyst - Pearlescent	14531	622	20	0.000	A	A	A	A
Sky Blue - Pearlescent	14532	622	21	0.000	A	A	A	A
Stormy Sea - Pearlescent - TMT original	14533	622	22	0.000	A	A	A	A
Teal - Pearlescent - TMT original	14534	622	23	0.000	A	A	A	A
Turquoise - Pearlescent	14535	622	24	0.000	A	A	A	A
Solid White - Solid color - no shimmer	14536	622	25	0.000	A	A	A	A
Solid Blue - Solid color - no shimmer	14537	622	26	0.000	A	A	A	A
Solid Brown - Solid color - no shimmer	14538	622	27	0.000	A	A	A	A
Solid Black - Solid color - no shimmer	14539	622	28	0.000	A	A	A	A
Solid Red - Solid color - no shimmer	14540	622	29	0.000	A	A	A	A
Solid Yellow - Solid color - no shimmer	14541	622	30	0.000	A	A	A	A
Solid Green - Solid color - no shimmer	14542	622	31	0.000	A	A	A	A
Solid Purple - Solid color - no shimmer	14543	622	32	0.000	A	A	A	A
Solid Cyan - Solid color - no shimmer	14544	622	33	0.000	A	A	A	A
Solid Brown - Solid color - no shimmer	14545	622	34	0.000	A	A	A	A
Solid Blood Red - Solid color - no shimmer	14546	622	35	0.000	A	A	A	A
Solid Light Flesh - Solid color - no shimmer	14547	622	36	0.000	A	A	A	A
Solid Medium Flesh - Solid color - no shimmer	14548	622	37	0.000	A	A	A	A
Solid Dark Flesh - Solid color - no shimmer	14549	622	38	0.000	A	A	A	A
Neon Pink - Solid color - extra bright	14550	622	39	0.000	A	A	A	A
Neon Yellow - Solid color - extra bright	14551	622	40	0.000	A	A	A	A
Neon Orange - Solid color - extra bright	14552	622	41	0.000	A	A	A	A
Neon Green - Solid color - not very pow	14553	622	42	0.000	A	A	A	A
Neon Blue - Solid color - not very pow	14554	622	43	0.000	A	A	A	A
Neon Purple - Solid color - not very pow	14555	622	44	0.000	A	A	A	A