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 = 'de' 
WHERE 
  1 
  AND a.option_id IN (
    4, 5, 6, 7, 20, 21, 22, 23, 41, 42, 43, 44, 
    45
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00103

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.411272723,
    "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": 59.12806702,
              "attached_condition": "a.option_id in (4,5,6,7,20,21,22,23,41,42,43,44,45) 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": 217,
          "rows": 1,
          "cost": 0.19818948,
          "filtered": 100,
          "attached_condition": "trigcond(b.lang_code = 'de')"
        }
      }
    ]
  }
}

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
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
Peanuts	185	41	10	0.000	A	A	A	A
Peanuts	216	42	10	0.000	A	A	A	A
Peanuts	247	43	10	0.000	A	A	A	A
Peanuts	278	44	10	0.000	A	A	A	A
Peanuts	309	45	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
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
Almonds	186	41	20	0.000	A	A	A	A
Almonds	217	42	20	0.000	A	A	A	A
Almonds	248	43	20	0.000	A	A	A	A
Almonds	279	44	20	0.000	A	A	A	A
Almonds	310	45	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
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
Sesame	187	41	30	0.000	A	A	A	A
Sesame	218	42	30	0.000	A	A	A	A
Sesame	249	43	30	0.000	A	A	A	A
Sesame	280	44	30	0.000	A	A	A	A
Sesame	311	45	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
Peacans	188	41	40	0.000	A	A	A	A
Peacans	219	42	40	0.000	A	A	A	A
Peacans	250	43	40	0.000	A	A	A	A
Peacans	281	44	40	0.000	A	A	A	A
Peacans	312	45	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
Macadamia	189	41	50	0.000	A	A	A	A
Macadamia	220	42	50	0.000	A	A	A	A
Macadamia	251	43	50	0.000	A	A	A	A
Macadamia	282	44	50	0.000	A	A	A	A
Macadamia	313	45	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
Hazelnut	190	41	60	0.000	A	A	A	A
Hazelnut	221	42	60	0.000	A	A	A	A
Hazelnut	252	43	60	0.000	A	A	A	A
Hazelnut	283	44	60	0.000	A	A	A	A
Hazelnut	314	45	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
Pistachios	191	41	70	0.000	A	A	A	A
Pistachios	222	42	70	0.000	A	A	A	A
Pistachios	253	43	70	0.000	A	A	A	A
Pistachios	284	44	70	0.000	A	A	A	A
Pistachios	315	45	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
Trail Mix	192	41	80	0.000	A	A	A	A
Trail Mix	223	42	80	0.000	A	A	A	A
Trail Mix	254	43	80	0.000	A	A	A	A
Trail Mix	285	44	80	0.000	A	A	A	A
Trail Mix	316	45	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
Orange	193	41	90	0.000	A	A	A	A
Orange	224	42	90	0.000	A	A	A	A
Orange	255	43	90	0.000	A	A	A	A
Orange	286	44	90	0.000	A	A	A	A
Orange	317	45	90	0.000	A	A	A	A
Sultanas	194	41	100	0.000	A	A	A	A
Sultanas	225	42	100	0.000	A	A	A	A
Sultanas	256	43	100	0.000	A	A	A	A
Sultanas	287	44	100	0.000	A	A	A	A
Sultanas	318	45	100	0.000	A	A	A	A
Prunes	195	41	110	0.000	A	A	A	A
Prunes	226	42	110	0.000	A	A	A	A
Prunes	257	43	110	0.000	A	A	A	A
Prunes	288	44	110	0.000	A	A	A	A
Prunes	319	45	110	0.000	A	A	A	A
Apricot	196	41	120	0.000	A	A	A	A
Apricot	227	42	120	0.000	A	A	A	A
Apricot	258	43	120	0.000	A	A	A	A
Apricot	289	44	120	0.000	A	A	A	A
Apricot	320	45	120	0.000	A	A	A	A
Coconut	197	41	130	0.000	A	A	A	A
Coconut	228	42	130	0.000	A	A	A	A
Coconut	259	43	130	0.000	A	A	A	A
Coconut	290	44	130	0.000	A	A	A	A
Coconut	321	45	130	0.000	A	A	A	A
Lemon Zest	198	41	140	0.000	A	A	A	A
Lemon Zest	229	42	140	0.000	A	A	A	A
Lemon Zest	260	43	140	0.000	A	A	A	A
Lemon Zest	291	44	140	0.000	A	A	A	A
Lemon Zest	322	45	140	0.000	A	A	A	A
Strawberry	199	41	150	0.000	A	A	A	A
Strawberry	230	42	150	0.000	A	A	A	A
Strawberry	261	43	150	0.000	A	A	A	A
Strawberry	292	44	150	0.000	A	A	A	A
Strawberry	323	45	150	0.000	A	A	A	A
Cranberry	200	41	160	0.000	A	A	A	A
Cranberry	231	42	160	0.000	A	A	A	A
Cranberry	262	43	160	0.000	A	A	A	A
Cranberry	293	44	160	0.000	A	A	A	A
Cranberry	324	45	160	0.000	A	A	A	A
Fruit mix	201	41	170	0.000	A	A	A	A
Fruit mix	232	42	170	0.000	A	A	A	A
Fruit mix	263	43	170	0.000	A	A	A	A
Fruit mix	294	44	170	0.000	A	A	A	A
Fruit mix	325	45	170	0.000	A	A	A	A
Vanilla	202	41	180	0.000	A	A	A	A
Vanilla	233	42	180	0.000	A	A	A	A
Vanilla	264	43	180	0.000	A	A	A	A
Vanilla	295	44	180	0.000	A	A	A	A
Vanilla	326	45	180	0.000	A	A	A	A
Shortbread	203	41	190	0.000	A	A	A	A
Shortbread	234	42	190	0.000	A	A	A	A
Shortbread	265	43	190	0.000	A	A	A	A
Shortbread	296	44	190	0.000	A	A	A	A
Shortbread	327	45	190	0.000	A	A	A	A
Malted Milk	204	41	200	0.000	A	A	A	A
Malted Milk	235	42	200	0.000	A	A	A	A
Malted Milk	266	43	200	0.000	A	A	A	A
Malted Milk	297	44	200	0.000	A	A	A	A
Malted Milk	328	45	200	0.000	A	A	A	A
Buttermilk	205	41	210	0.000	A	A	A	A
Buttermilk	236	42	210	0.000	A	A	A	A
Buttermilk	267	43	210	0.000	A	A	A	A
Buttermilk	298	44	210	0.000	A	A	A	A
Buttermilk	329	45	210	0.000	A	A	A	A
Butterscotch	206	41	220	0.000	A	A	A	A
Butterscotch	237	42	220	0.000	A	A	A	A
Butterscotch	268	43	220	0.000	A	A	A	A
Butterscotch	299	44	220	0.000	A	A	A	A
Butterscotch	330	45	220	0.000	A	A	A	A
Chocolate	207	41	230	0.000	A	A	A	A
Chocolate	238	42	230	0.000	A	A	A	A
Chocolate	269	43	230	0.000	A	A	A	A
Chocolate	300	44	230	0.000	A	A	A	A
Chocolate	331	45	230	0.000	A	A	A	A
Salted Caramel	208	41	240	0.000	A	A	A	A
Salted Caramel	239	42	240	0.000	A	A	A	A
Salted Caramel	270	43	240	0.000	A	A	A	A
Salted Caramel	301	44	240	0.000	A	A	A	A
Salted Caramel	332	45	240	0.000	A	A	A	A
Baileys	209	41	250	0.000	A	A	A	A
Baileys	240	42	250	0.000	A	A	A	A
Baileys	271	43	250	0.000	A	A	A	A
Baileys	302	44	250	0.000	A	A	A	A
Baileys	333	45	250	0.000	A	A	A	A
Ginger	210	41	260	0.000	A	A	A	A
Ginger	241	42	260	0.000	A	A	A	A
Ginger	272	43	260	0.000	A	A	A	A
Ginger	303	44	260	0.000	A	A	A	A
Ginger	334	45	260	0.000	A	A	A	A
Nutmeg	211	41	270	0.000	A	A	A	A
Nutmeg	242	42	270	0.000	A	A	A	A
Nutmeg	273	43	270	0.000	A	A	A	A
Nutmeg	304	44	270	0.000	A	A	A	A
Nutmeg	335	45	270	0.000	A	A	A	A
Cinnamon	212	41	280	0.000	A	A	A	A
Cinnamon	243	42	280	0.000	A	A	A	A
Cinnamon	274	43	280	0.000	A	A	A	A
Cinnamon	305	44	280	0.000	A	A	A	A
Cinnamon	336	45	280	0.000	A	A	A	A
Chilli Pepper	213	41	290	0.000	A	A	A	A
Chilli Pepper	244	42	290	0.000	A	A	A	A
Chilli Pepper	275	43	290	0.000	A	A	A	A
Chilli Pepper	306	44	290	0.000	A	A	A	A
Chilli Pepper	337	45	290	0.000	A	A	A	A
Black Pepper	214	41	300	0.000	A	A	A	A
Black Pepper	245	42	300	0.000	A	A	A	A
Black Pepper	276	43	300	0.000	A	A	A	A
Black Pepper	307	44	300	0.000	A	A	A	A
Black Pepper	338	45	300	0.000	A	A	A	A
Suya Spice	215	41	310	0.000	A	A	A	A
Suya Spice	246	42	310	0.000	A	A	A	A
Suya Spice	277	43	310	0.000	A	A	A	A
Suya Spice	308	44	310	0.000	A	A	A	A
Suya Spice	339	45	310	0.000	A	A	A	A