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 (
    126, 276, 277, 278, 279, 280, 211, 160, 
    161, 159, 157, 158, 197, 234, 388, 389, 
    390, 391, 162, 202, 203, 207, 208, 186, 
    185, 468, 469, 645, 292, 293, 1074, 1075, 
    1076, 1072, 1073
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00424

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "365.16"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "198.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": 198,
            "rows_produced_per_join": 198,
            "filtered": "100.00",
            "index_condition": "((`cscartdb`.`a`.`option_id` in (126,276,277,278,279,280,211,160,161,159,157,158,197,234,388,389,390,391,162,202,203,207,208,186,185,468,469,645,292,293,1074,1075,1076,1072,1073)) and (`cscartdb`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "78.06",
              "eval_cost": "19.80",
              "prefix_cost": "97.86",
              "data_read_per_join": "9K"
            },
            "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": 198,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "49.50",
              "eval_cost": "19.80",
              "prefix_cost": "167.16",
              "data_read_per_join": "151K"
            },
            "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
Black	5174	158	0	0.000	A	A	A	A
Dark	5175	158	0	0.000	A	A	A	A
Light	5176	158	0	0.000	A	A	A	A
Red	5177	158	0	0.000	A	A	A	A
Custom (add to notes)	5224	161	0	0.000	A	A	A	A
Small (5.5"-7.5")	5225	161	0	0.000	A	A	A	A
Medium (8"-10")	5226	161	0	0.000	A	A	A	A
Large (10.5"-12.5")	5227	161	0	0.000	A	A	A	A
Custom (add to notes)	5228	162	0	0.000	A	A	A	A
Small (5.5"-7.5")	5229	162	0	0.000	A	A	A	A
Medium (8"-10")	5230	162	0	0.000	A	A	A	A
Large (10.5"-12.5")	5231	162	0	0.000	A	A	A	A
18"	7278	185	0	0.000	A	A	A	A
24"	7279	185	0	0.000	A	A	A	A
30" (default)	7280	185	0	0.000	A	A	A	A
36"	7281	185	0	0.000	A	A	A	A
Custom - (any length between 16" - 36")	7282	185	0	0.000	A	A	A	A
Short (6")	7639	207	0	0.000	A	A	A	A
Medium (8")	7640	207	0	0.000	A	A	A	A
Long (10")	7641	207	0	0.000	A	A	A	A
Brass	8043	159	0	0.000	A	A	A	A
Polished Nickel	8044	159	0	0.000	A	A	A	A
Matte Nickel	8045	159	0	0.000	A	A	A	A
Black Blossom	9103	234	0	0.000	A	A	A	A
Brown Blossom	9104	234	0	0.000	A	A	A	A
Red Blossom	9105	234	0	0.000	A	A	A	A
Pink Blossom	9106	234	0	0.000	A	A	A	A
Turquoise	10379	277	0	0.000	A	A	A	A
Yellow	10380	277	0	0.000	A	A	A	A
Blue	10381	277	0	0.000	A	A	A	A
White	10382	277	0	0.000	A	A	A	A
English Toast	10383	277	0	0.000	A	A	A	A
Purple	10384	277	0	0.000	A	A	A	A
Hunter Green	10385	277	0	0.000	A	A	A	A
Red	10386	277	0	0.000	A	A	A	A
Hot Pink	10387	277	0	0.000	A	A	A	A
Black	10388	277	0	0.000	A	A	A	A
Club Default 6-9" range	10416	278	0	0.000	A	A	A	A
Club Default Size (~7" to 11")	10479	279	0	0.000	A	A	A	A
Panic Snap (default)	10480	280	0	0.000	A	A	A	A
Rated Shackle	10481	280	0	240.000	A	A	A	A
Heavy D-Ring	10482	280	0	0.000	A	A	A	A
Black	5194	157	1	0.000	A	A	A	A
Custom (add to notes)	5218	160	1	0.000	A	A	A	A
Oak (Natural)	7419	186	1	0.000	A	A	A	A
Rivets	7642	208	1	0.000	A	A	A	A
Custom (add to notes)	7734	197	1	0.000	A	A	A	A
Custom (add to notes)	7738	203	1	0.000	A	A	A	A
Custom (add to notes)	7793	202	1	0.000	A	A	A	A
Custom (add to notes)	8042	211	1	0.000	A	A	A	A
Black	10366	276	1	0.000	A	A	A	A
3.5	10400	278	1	0.000	A	A	A	A
5 inch	10450	279	1	0.000	A	A	A	A
Medium Small Heart Lock	10535	292	1	0.000	A	A	A	A
Cup Holder	11936	388	1	15.000	A	A	A	A
Level	11937	389	1	10.000	A	A	A	A
Sedona Red	11941	391	1	0.000	A	A	A	A
No Fractal Burn	12008	390	1	0.000	A	A	A	A
Bolt Snaps & O-rings	14310	645	1	0.000	A	A	A	A
Standard Size (6"--8.5" wrists; 8--10.5" ankles)	14853	468	1	0.000	A	A	A	A
Black locking (default)	16749	126	1	0.000	A	A	A	A
Standard Buckles	18683	469	1	0.000	A	A	A	A
No	18953	1075	1	0.000	A	A	A	A
No	18964	1073	1	0.000	A	A	A	A
No	18966	1072	1	0.000	A	A	A	A
No	18968	1076	1	0.000	A	A	A	A
S 23.5"-28.5"	18980	1074	1	0.000	A	A	A	A
Natural	5195	157	2	0.000	A	A	A	A
XSmall (11.5"-13.5")	5219	160	2	0.000	A	A	A	A
Oak (Carrington)	7420	186	2	0.000	A	A	A	A
Sewn	7643	208	2	25.000	A	A	A	A
Small (5.5" - 7.5")	7735	197	2	0.000	A	A	A	A
XS (22" - 24")	7739	203	2	0.000	A	A	A	A
XXS (14" - 16")	7794	202	2	0.000	A	A	A	A
XSmall (9" - 11")	8037	211	2	0.000	A	A	A	A
White	10367	276	2	5.000	A	A	A	A
3.75	10411	278	2	0.000	A	A	A	A
5.25 inch	10452	279	2	0.000	A	A	A	A
Medium Small Silver Padlock	10536	292	2	0.000	A	A	A	A
5	10561	293	2	0.000	A	A	A	A
Aged Oak	11942	391	2	0.000	A	A	A	A
No Level	12010	389	2	0.000	A	A	A	A
Scissor Clips & O-rings	14311	645	2	0.000	A	A	A	A
Custom Size (leave exact wrist & ankle size in Notes)	14854	468	2	0.000	A	A	A	A
Black non-locking	16750	126	2	0.000	A	A	A	A
Lockable w/Padlocks	18684	469	2	27.600	A	A	A	A
S 19.0"-22.5"	18954	1075	2	220.000	A	A	A	A
Yes Include Stocking clips	18965	1073	2	60.000	A	A	A	A
Yes Include Garter Straps	18967	1072	2	100.000	A	A	A	A
Yes Include Wand Holder	18969	1076	2	100.000	A	A	A	A
M 27.5"-32.5"	18981	1074	2	0.000	A	A	A	A
Light Brown	5196	157	3	0.000	A	A	A	A
Small (14"-16")	5220	160	3	0.000	A	A	A	A
Oak (Kona)	7421	186	3	0.000	A	A	A	A
Medium (8" - 10")	7736	197	3	0.000	A	A	A	A
S (25" - 27")	7740	203	3	0.000	A	A	A	A
XS (17" - 19")	7795	202	3	0.000	A	A	A	A
Small (11.5" - 13.5")	8038	211	3	0.000	A	A	A	A
Antique Brown.	10368	276	3	10.000	A	A	A	A
4	10399	278	3	0.000	A	A	A	A
5.5 inch	10453	279	3	0.000	A	A	A	A
Medium Small Gold Heartlock	10537	292	3	0.000	A	A	A	A
5.5	10560	293	3	0.000	A	A	A	A
Fractal Burning - Large	11940	390	3	150.000	A	A	A	A
Ebony	11943	391	3	0.000	A	A	A	A
No Cup Holder	12009	388	3	0.000	A	A	A	A
Bolt Snaps on both ends	14312	645	3	12.500	A	A	A	A
nickel locking	16751	126	3	0.000	A	A	A	A
Lockable-NO Padlocks	18685	469	3	7.600	A	A	A	A
M 22.0"-25.5"	18955	1075	3	220.000	A	A	A	A
L 31.5"-38.0"	18982	1074	3	0.000	A	A	A	A
Medium Brown	5197	157	4	0.000	A	A	A	A
Medium (16.5"-18.5")	5221	160	4	0.000	A	A	A	A
Padauk	7422	186	4	18.000	A	A	A	A
Large (10.5" - 12.5")	7737	197	4	0.000	A	A	A	A
M (28" - 30")	7741	203	4	0.000	A	A	A	A
S (20" - 22")	7796	202	4	0.000	A	A	A	A
Medium (14" - 16")	8039	211	4	0.000	A	A	A	A
Purple Latigo	10373	276	4	5.000	A	A	A	A
4.25	10410	278	4	0.000	A	A	A	A
5.75 inch	10454	279	4	0.000	A	A	A	A
Large small Silver Padlock	10538	292	4	0.000	A	A	A	A
6	10545	293	4	0.000	A	A	A	A
Honey	11944	391	4	0.000	A	A	A	A
Scissor Clips on both ends	14313	645	4	12.500	A	A	A	A
nickel non-locking	16752	126	4	0.000	A	A	A	A
L 25.0"-28.5"	18956	1075	4	220.000	A	A	A	A
XL 37.5"-44.0"	18983	1074	4	0.000	A	A	A	A
Mahogany	5198	157	5	0.000	A	A	A	A
Large (19"-21")	5222	160	5	0.000	A	A	A	A
Mahogany	7423	186	5	18.000	A	A	A	A
L (31" - 33")	7742	203	5	0.000	A	A	A	A
M (23" - 25")	7797	202	5	0.000	A	A	A	A
Large (16.5" - 18.5")	8040	211	5	0.000	A	A	A	A
Hot Pink Latigo	10372	276	5	5.000	A	A	A	A
4.5	10398	278	5	0.000	A	A	A	A
6 inch	10455	279	5	0.000	A	A	A	A
Large Small Gold Heart Padlock	10539	292	5	0.000	A	A	A	A
6.5	10548	293	5	0.000	A	A	A	A
Mahogany	11945	391	5	0.000	A	A	A	A
brass locking	16753	126	5	0.000	A	A	A	A
XL 28.0"-31.5"	18957	1075	5	220.000	A	A	A	A
No	18984	1074	5	-220.000	A	A	A	A
Purple	5199	157	6	0.000	A	A	A	A
XLarge (21.5"-23.5")	5223	160	6	0.000	A	A	A	A
Walnut	7424	186	6	30.000	A	A	A	A
XL (34" - 36")	7743	203	6	0.000	A	A	A	A
L (26" - 28")	7798	202	6	0.000	A	A	A	A
XLarge (19" - 21")	8041	211	6	0.000	A	A	A	A
Blue Latigo	10374	276	6	5.000	A	A	A	A
4.75	10409	278	6	0.000	A	A	A	A
6.25 inch	10456	279	6	0.000	A	A	A	A
15mm Silver Padlock	10540	292	6	0.000	A	A	A	A
7	10546	293	6	0.000	A	A	A	A
Classic Grey	11946	391	6	0.000	A	A	A	A
brass non-locking	16754	126	6	0.000	A	A	A	A
Oxblood (Pink)	5200	157	7	0.000	A	A	A	A
Purpleheart	7425	186	7	54.000	A	A	A	A
XXL (37" - 39")	7744	203	7	0.000	A	A	A	A
XL (29" - 31")	7799	202	7	0.000	A	A	A	A
Navy Blue	10378	276	7	0.000	A	A	A	A
5	10397	278	7	0.000	A	A	A	A
6.5 inch	10457	279	7	0.000	A	A	A	A
15mm Gold Padlock	10541	292	7	0.000	A	A	A	A
7.5	10549	293	7	0.000	A	A	A	A
Dark Walnut	11947	391	7	0.000	A	A	A	A
Pink Locking	16755	126	7	0.000	A	A	A	A
Red	5201	157	8	0.000	A	A	A	A
XXL (32" - 34")	7800	202	8	0.000	A	A	A	A
Red Latigo	10371	276	8	5.000	A	A	A	A
5.25	10408	278	8	0.000	A	A	A	A
6.75 inch	10458	279	8	0.000	A	A	A	A
Black Decrative padlock	10542	292	8	0.000	A	A	A	A
8	10547	293	8	0.000	A	A	A	A
Red Oak	11948	391	8	0.000	A	A	A	A
Pink Non-Locking	16756	126	8	0.000	A	A	A	A
[Vegan] Brown	5202	157	9	0.000	A	A	A	A
Jade Green Latigo	10375	276	9	5.000	A	A	A	A
5.5	10396	278	9	0.000	A	A	A	A
7 inch	10459	279	9	0.000	A	A	A	A
Brass Padlock	10543	292	9	0.000	A	A	A	A
8.5	10550	293	9	0.000	A	A	A	A
Golden Oak	11949	391	9	0.000	A	A	A	A
Red Locking	16757	126	9	0.000	A	A	A	A
[Vegan] Black	5203	157	10	0.000	A	A	A	A
Yellow Latigo	10377	276	10	5.000	A	A	A	A
5.75	10407	278	10	0.000	A	A	A	A
7.25 inch	10460	279	10	0.000	A	A	A	A
9	10551	293	10	0.000	A	A	A	A
No Stain - Sanding Only	11950	391	10	0.000	A	A	A	A
Red Non-Locking	16758	126	10	0.000	A	A	A	A
Saddle Red (chestnut)	10376	276	11	0.000	A	A	A	A
6	10395	278	11	0.000	A	A	A	A
7.5 inch	10461	279	11	0.000	A	A	A	A
9.5	10552	293	11	0.000	A	A	A	A
Purple Locking	16759	126	11	0.000	A	A	A	A
Gold	10369	276	12	10.000	A	A	A	A
6.25	10406	278	12	0.000	A	A	A	A
7.75 inch	10462	279	12	0.000	A	A	A	A
10	10553	293	12	0.000	A	A	A	A
Purple Non-locking	16760	126	12	0.000	A	A	A	A
Silver	10370	276	13	10.000	A	A	A	A
6.5	10394	278	13	0.000	A	A	A	A
8 inch	10463	279	13	0.000	A	A	A	A
10.5	10554	293	13	0.000	A	A	A	A
Blue Locking	16761	126	13	0.000	A	A	A	A
6.75	10405	278	14	0.000	A	A	A	A
8.25 inch	10464	279	14	0.000	A	A	A	A
11	10555	293	14	0.000	A	A	A	A
Blue Non-Locking	16762	126	14	0.000	A	A	A	A
7	10393	278	15	0.000	A	A	A	A
8.5 inch	10465	279	15	0.000	A	A	A	A
11.5	10556	293	15	0.000	A	A	A	A
Green Locking	16763	126	15	0.000	A	A	A	A
7.25	10404	278	16	0.000	A	A	A	A
8.75 inch	10466	279	16	0.000	A	A	A	A
12	10557	293	16	0.000	A	A	A	A
Green Non-Locking	16764	126	16	0.000	A	A	A	A
7.5	10392	278	17	0.000	A	A	A	A
9 inch	10467	279	17	0.000	A	A	A	A
12.5	10558	293	17	0.000	A	A	A	A
Rainbow locking	16765	126	17	0.000	A	A	A	A
7.75	10403	278	18	0.000	A	A	A	A
9.25 inch	10468	279	18	0.000	A	A	A	A
13	10559	293	18	0.000	A	A	A	A
8	10391	278	19	0.000	A	A	A	A
9.5 inch	10469	279	19	0.000	A	A	A	A
8.25	10402	278	20	0.000	A	A	A	A
9.75 inch	10470	279	20	0.000	A	A	A	A
8.5	10390	278	21	0.000	A	A	A	A
10 inch	10471	279	21	0.000	A	A	A	A
8.75	10401	278	22	0.000	A	A	A	A
10.25 inch	10472	279	22	0.000	A	A	A	A
9	10389	278	23	0.000	A	A	A	A
10.5 inch	10473	279	23	0.000	A	A	A	A
9.25	10412	278	24	0.000	A	A	A	A
10.75 inch	10474	279	24	0.000	A	A	A	A
9.5	10413	278	25	0.000	A	A	A	A
11 inch	10475	279	25	0.000	A	A	A	A
9.75	10414	278	26	0.000	A	A	A	A
11.25 inch	10476	279	26	0.000	A	A	A	A
10	10415	278	27	0.000	A	A	A	A
11.5 inch	10477	279	27	0.000	A	A	A	A
11.75 inch	10478	279	28	0.000	A	A	A	A
12 inch	10451	279	29	0.000	A	A	A	A