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 (
    1030, 1031, 698, 697, 1039, 1040, 1335, 
    1503, 187, 1502, 590, 591, 188, 1326, 
    602, 603, 1032, 1033, 1513, 1034, 1035, 
    1037, 1038, 862, 863, 1042, 1043
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.01351

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "read_sorted_file": {
          "filesort": {
            "sort_key": "a.position, a.variant_id",
            "table": {
              "table_name": "a",
              "access_type": "range",
              "possible_keys": ["status", "option_id", "option_id_2"],
              "key": "option_id",
              "key_length": "6",
              "used_key_parts": ["option_id", "status"],
              "rows": 104,
              "filtered": 100,
              "index_condition": "a.option_id in (1030,1031,698,697,1039,1040,1335,1503,187,1502,590,591,188,1326,602,603,1032,1033,1513,1034,1035,1037,1038,862,863,1042,1043) 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": ["dev_db.a.variant_id", "const"],
          "rows": 1,
          "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
Gift box with a greeting card and matches	232	187	0	25000.000	A	A	A	A
Gift box	233	188	0	20000.000	A	A	A	A
Sea Salt	870	590	0	0.000	A	A	A	A
Mango	871	590	0	0.000	A	A	A	A
Tropical	872	590	0	0.000	A	A	A	A
T-Peach	873	590	0	0.000	A	A	A	A
Sea Salt	874	591	0	0.000	A	A	A	A
Mango	875	591	0	0.000	A	A	A	A
Tropical	876	591	0	0.000	A	A	A	A
T-Peach	877	591	0	0.000	A	A	A	A
Be mine	906	602	0	0.000	A	A	A	A
Mean to me	907	602	0	0.000	A	A	A	A
Call me	908	602	0	0.000	A	A	A	A
I love you	909	602	0	0.000	A	A	A	A
Be mine	913	603	0	0.000	A	A	A	A
Mean to me	914	603	0	0.000	A	A	A	A
Call me	915	603	0	0.000	A	A	A	A
I love you	916	603	0	0.000	A	A	A	A
Matchbox	3580	1335	0	10000.000	A	A	A	A
Gift box with a greeting card and matches	3935	1502	0	20000.000	A	A	A	A
Gift Wrapping	3959	1513	0	20000.000	A	A	A	A
Home is where mom is.	1474	697	1	0.000	A	A	A	A
Twilight	1478	698	1	0.000	A	A	A	A
Coffee	2015	862	1	0.000	A	A	A	A
Small gift box	2024	863	1	20000.000	A	A	A	A
Soulmate	2455	1030	1	0.000	A	A	A	A
Soulmate	2459	1031	1	0.000	A	A	A	A
Soulmate	2463	1032	1	0.000	A	A	A	A
Soulmate	2467	1033	1	0.000	A	A	A	A
Sandalwood	2471	1034	1	0.000	A	A	A	A
Sandalwood	2475	1035	1	0.000	A	A	A	A
Laziness	2486	1037	1	0.000	A	A	A	A
Laziness	2491	1038	1	0.000	A	A	A	A
Enchant	2496	1039	1	0.000	A	A	A	A
Enchant	2501	1040	1	0.000	A	A	A	A
Sea Salt	2510	1042	1	0.000	A	A	A	A
Sea Salt	2514	1043	1	0.000	A	A	A	A
Basic Gift Box included Greeting Card and Stickers	3553	1326	1	15000.000	A	A	A	A
Enchant	3936	1503	1	0.000	A	A	A	A
You are my sunshine.	1475	697	2	0.000	A	A	A	A
Hygge	1479	698	2	0.000	A	A	A	A
Chillin	2016	862	2	0.000	A	A	A	A
Big gift box	2025	863	2	30000.000	A	A	A	A
Xoxo	2456	1030	2	0.000	A	A	A	A
Xoxo	2460	1031	2	0.000	A	A	A	A
Xoxo	2464	1032	2	0.000	A	A	A	A
Xoxo	2468	1033	2	0.000	A	A	A	A
Coffee	2472	1034	2	0.000	A	A	A	A
Coffee	2476	1035	2	0.000	A	A	A	A
Twilight	2487	1037	2	0.000	A	A	A	A
Twilight	2492	1038	2	0.000	A	A	A	A
Xanadu	2497	1039	2	0.000	A	A	A	A
Xanadu	2502	1040	2	0.000	A	A	A	A
Mango	2511	1042	2	0.000	A	A	A	A
Mango	2515	1043	2	0.000	A	A	A	A
Basic Gift box with Matches	3554	1326	2	40000.000	A	A	A	A
Xanadu	3937	1503	2	0.000	A	A	A	A
Love you, always.	1476	697	3	0.000	A	A	A	A
Glamour	1480	698	3	0.000	A	A	A	A
Mimosa	2017	862	3	0.000	A	A	A	A
Match box	2026	863	3	10000.000	A	A	A	A
Simple Love	2457	1030	3	0.000	A	A	A	A
Simple Love	2461	1031	3	0.000	A	A	A	A
Simple Love	2465	1032	3	0.000	A	A	A	A
Simple Love	2469	1033	3	0.000	A	A	A	A
Jasmine	2473	1034	3	0.000	A	A	A	A
Jasmine	2477	1035	3	0.000	A	A	A	A
Hygge	2488	1037	3	0.000	A	A	A	A
Hygge	2493	1038	3	0.000	A	A	A	A
Chillin	2498	1039	3	0.000	A	A	A	A
Chillin	2503	1040	3	0.000	A	A	A	A
Tropical	2512	1042	3	0.000	A	A	A	A
Tropical	2516	1043	3	0.000	A	A	A	A
Basic Gift box with Hanging Wax Sachet	3555	1326	3	70000.000	A	A	A	A
Chillin	3938	1503	3	0.000	A	A	A	A
See how much I love you.	1477	697	4	0.000	A	A	A	A
Paradise	1481	698	4	0.000	A	A	A	A
Honey	2018	862	4	0.000	A	A	A	A
Without You	2458	1030	4	0.000	A	A	A	A
Without You	2462	1031	4	0.000	A	A	A	A
Without You	2466	1032	4	0.000	A	A	A	A
Without You	2470	1033	4	0.000	A	A	A	A
Grapefruit	2474	1034	4	0.000	A	A	A	A
Grapefruit	2478	1035	4	0.000	A	A	A	A
Glamour	2489	1037	4	0.000	A	A	A	A
Glamour	2494	1038	4	0.000	A	A	A	A
Wooden	2499	1039	4	0.000	A	A	A	A
Wooden	2504	1040	4	0.000	A	A	A	A
T-Peach	2513	1042	4	0.000	A	A	A	A
T-Peach	2517	1043	4	0.000	A	A	A	A
Wooden	3939	1503	4	0.000	A	A	A	A
Delighted	1482	698	5	0.000	A	A	A	A
Twilight	2019	862	5	0.000	A	A	A	A
Delighted	2490	1037	5	0.000	A	A	A	A
Delighted	2495	1038	5	0.000	A	A	A	A
Yuque	2500	1039	5	0.000	A	A	A	A
Yuque	2505	1040	5	0.000	A	A	A	A
Yuque	3940	1503	5	0.000	A	A	A	A
Black vani	1483	698	6	0.000	A	A	A	A
Yuque	2020	862	6	0.000	A	A	A	A
Laziness	1484	698	7	0.000	A	A	A	A
Laziness	2021	862	7	0.000	A	A	A	A
Tobacco	1485	698	8	0.000	A	A	A	A
Mango	2022	862	8	0.000	A	A	A	A
Jasmine	2023	862	9	0.000	A	A	A	A