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 (6, 20, 5, 22, 4, 21, 7, 23, 8) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00071

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.176468928,
    "nested_loop": [
      {
        "read_sorted_file": {
          "filesort": {
            "sort_key": "a.position, a.variant_id",
            "table": {
              "table_name": "a",
              "access_type": "ALL",
              "possible_keys": ["status", "option_id", "option_id_2"],
              "loops": 1,
              "rows": 367,
              "cost": 0.0723854,
              "filtered": 17.7111721,
              "attached_condition": "a.option_id in (6,20,5,22,4,21,7,23,8) and a.`status` = 'A'"
            }
          }
        }
      },
      {
        "table": {
          "table_name": "b",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["variant_id", "lang_code"],
          "ref": ["u428615623_ecartifygonje.a.variant_id", "const"],
          "loops": 65,
          "rows": 1,
          "cost": 0.0622346,
          "filtered": 100,
          "attached_condition": "trigcond(b.lang_code = 'en')"
        }
      }
    ]
  }
}

Result

variant_name	variant_id	option_id	position	modifier	modifier_type	weight_modifier_type	point_modifier_type	status
Peanuts	9	4	10	0.000	A	A	A	A
Orange	17	5	10	0.000	A	A	A	A
Vanilla	26	6	10	0.000	A	A	A	A
Ginger	34	7	10	0.000	A	A	A	A
1 Pack	40	8	10	0.000	A	A	A	A
Vanilla	90	20	10	0.000	A	A	A	A
Peanuts	98	21	10	0.000	A	A	A	A
Orange	106	22	10	0.000	A	A	A	A
Ginger	115	23	10	0.000	A	A	A	A
Almonds	10	4	20	0.000	A	A	A	A
Sultanas	18	5	20	0.000	A	A	A	A
Shortbread	32	6	20	0.000	A	A	A	A
Nutmeg	35	7	20	0.000	A	A	A	A
5 Pack	41	8	20	40.000	A	A	A	A
Shortbread	91	20	20	0.000	A	A	A	A
Almonds	99	21	20	0.000	A	A	A	A
Sultanas	107	22	20	0.000	A	A	A	A
Nutmeg	116	23	20	0.000	A	A	A	A
Sesame	11	4	30	0.000	A	A	A	A
Prunes	19	5	30	0.000	A	A	A	A
Malted Milk	33	6	30	0.000	A	A	A	A
Cinnamon	36	7	30	0.000	A	A	A	A
10 Pack	42	8	30	90.000	A	A	A	A
Malted Milk	92	20	30	0.000	A	A	A	A
Sesame	100	21	30	0.000	A	A	A	A
Prunes	108	22	30	0.000	A	A	A	A
Cinnamon	117	23	30	0.000	A	A	A	A
Peacans	12	4	40	0.000	A	A	A	A
Apricot	20	5	40	0.000	A	A	A	A
Buttermilk	28	6	40	0.000	A	A	A	A
Chilli Pepper	37	7	40	0.000	A	A	A	A
Buttermilk	93	20	40	0.000	A	A	A	A
Peacans	101	21	40	0.000	A	A	A	A
Apricot	109	22	40	0.000	A	A	A	A
Chilli Pepper	118	23	40	0.000	A	A	A	A
Macadamia	13	4	50	0.000	A	A	A	A
Coconut	21	5	50	0.000	A	A	A	A
Butterscotch	29	6	50	0.000	A	A	A	A
Black Pepper	38	7	50	0.000	A	A	A	A
Butterscotch	94	20	50	0.000	A	A	A	A
Macadamia	102	21	50	0.000	A	A	A	A
Coconut	110	22	50	0.000	A	A	A	A
Black Pepper	119	23	50	0.000	A	A	A	A
Hazelnut	14	4	60	0.000	A	A	A	A
Lemon Zest	22	5	60	0.000	A	A	A	A
Chocolate	30	6	60	0.000	A	A	A	A
Suya Spice	39	7	60	0.000	A	A	A	A
Chocolate	95	20	60	0.000	A	A	A	A
Hazelnut	103	21	60	0.000	A	A	A	A
Lemon Zest	111	22	60	0.000	A	A	A	A
Suya Spice	120	23	60	0.000	A	A	A	A
Pistachios	15	4	70	0.000	A	A	A	A
Strawberry	23	5	70	0.000	A	A	A	A
Salted Caramel	31	6	70	0.000	A	A	A	A
Salted Caramel	96	20	70	0.000	A	A	A	A
Pistachios	104	21	70	0.000	A	A	A	A
Strawberry	112	22	70	0.000	A	A	A	A
Trail Mix	16	4	80	0.000	A	A	A	A
Cranberry	24	5	80	0.000	A	A	A	A
Baileys	27	6	80	6.000	A	A	A	A
Baileys	97	20	80	6.000	A	A	A	A
Trail Mix	105	21	80	0.000	A	A	A	A
Cranberry	113	22	80	0.000	A	A	A	A
Fruit mix	25	5	90	0.000	A	A	A	A
Fruit mix	114	22	90	0.000	A	A	A	A