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 (270, 257, 269, 260, 271, 265, 266) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00252

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "291.56"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "161.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": 161,
            "rows_produced_per_join": 161,
            "filtered": "100.00",
            "index_condition": "((`cscartdb`.`a`.`option_id` in (270,257,269,260,271,265,266)) and (`cscartdb`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "58.11",
              "eval_cost": "16.10",
              "prefix_cost": "74.21",
              "data_read_per_join": "7K"
            },
            "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": 161,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "40.25",
              "eval_cost": "16.10",
              "prefix_cost": "130.56",
              "data_read_per_join": "123K"
            },
            "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
Burgundy - Pearlescent	9458	260	0	0.000	A	A	A	A
French Rose – Pearlescent	9459	260	0	0.000	A	A	A	A
Tiger Orange – Pearlescent	9460	260	0	0.000	A	A	A	A
Deep Pink – Pearlescent	9461	260	0	0.000	A	A	A	A
Creamy Pink – Pearlescent	9462	260	0	0.000	A	A	A	A
Blue Iris – Pearlescent	9463	260	0	0.000	A	A	A	A
Blue Du France – Pearlescent	9464	260	0	0.000	A	A	A	A
Turquoise – Pearlescent	9465	260	0	0.000	A	A	A	A
Pearl White – Pearlescent	9466	260	0	0.000	A	A	A	A
Copper – Pearlescent	9467	260	0	0.000	A	A	A	A
Aureolin Yellow – Pearlescent	9468	260	0	0.000	A	A	A	A
Purple Heart – Pearlescent	9469	260	0	0.000	A	A	A	A
Robin Egg – Pearlescent	9470	260	0	0.000	A	A	A	A
Fandango Pink – Pearlescent	9471	260	0	0.000	A	A	A	A
Scarlet – Pearlescent	9472	260	0	0.000	A	A	A	A
Gold – Pearlescent	9473	260	0	0.000	A	A	A	A
Lilac – Pearlescent	9474	260	0	0.000	A	A	A	A
Maya Blue – Pearlescent	9475	260	0	0.000	A	A	A	A
Electric Blue – Pearlescent	9476	260	0	0.000	A	A	A	A
Persian Green – Pearlescent	9477	260	0	0.000	A	A	A	A
Parakeet Green – Pearlescent	9478	260	0	0.000	A	A	A	A
Blue Heart – Pearlescent	9479	260	0	0.000	A	A	A	A
Borealis Pink – Pearlescent	9480	260	0	0.000	A	A	A	A
Celeste Blue – Pearlescent	9481	260	0	0.000	A	A	A	A
Double Peony – Pearlescent	9482	260	0	0.000	A	A	A	A
Egyptian Green – Pearlescent	9483	260	0	0.000	A	A	A	A
Graphite Black – Pearlescent	9484	260	0	0.000	A	A	A	A
Snowflake White – Pearlescent	9485	260	0	0.000	A	A	A	A
Purple Flame – Pearlescent	9486	260	0	0.000	A	A	A	A
Coral Rose – Pearlescent	9487	260	0	0.000	A	A	A	A
Olive Gold – Pearlescent	9488	260	0	0.000	A	A	A	A
Canary Yellow – Pearlescent	9489	260	0	0.000	A	A	A	A
Turmeric Gold – Pearlescent	9490	260	0	0.000	A	A	A	A
Silver Ash – Pearlescent	9491	260	0	0.000	A	A	A	A
UV Orange	9492	260	0	0.000	A	A	A	A
UV Lemon	9493	260	0	0.000	A	A	A	A
UV Pink	9494	260	0	0.000	A	A	A	A
UV Green	9495	260	0	0.000	A	A	A	A
UV Blue	9496	260	0	0.000	A	A	A	A
UV Purple	9497	260	0	0.000	A	A	A	A
UV Lime	9498	260	0	0.000	A	A	A	A
UV Red	9499	260	0	0.000	A	A	A	A
Flat Black	9500	260	0	0.000	A	A	A	A
Flat Red	9501	260	0	0.000	A	A	A	A
Flat White	9502	260	0	0.000	A	A	A	A
Flat Purple	9503	260	0	0.000	A	A	A	A
Flat Blue	9504	260	0	0.000	A	A	A	A
Flat Green	9505	260	0	0.000	A	A	A	A
Flat Cyan	9506	260	0	0.000	A	A	A	A
Flat Yellow	9507	260	0	0.000	A	A	A	A
Soft 00-30	10027	270	0	0.000	A	A	A	A
Medium 00-50	10028	270	0	0.000	A	A	A	A
Firm Shore 10A	10029	270	0	0.000	A	A	A	A
None	10032	271	0	0.000	A	A	A	A
Burgundy - Pearlescent	10033	271	0	0.000	A	A	A	A
French Rose – Pearlescent	10034	271	0	0.000	A	A	A	A
Tiger Orange – Pearlescent	10035	271	0	0.000	A	A	A	A
Deep Pink – Pearlescent	10036	271	0	0.000	A	A	A	A
Creamy Pink – Pearlescent	10037	271	0	0.000	A	A	A	A
Blue Iris – Pearlescent	10038	271	0	0.000	A	A	A	A
Blue Du France – Pearlescent	10039	271	0	0.000	A	A	A	A
Turquoise – Pearlescent	10040	271	0	0.000	A	A	A	A
Pearl White – Pearlescent	10041	271	0	0.000	A	A	A	A
Copper – Pearlescent	10042	271	0	0.000	A	A	A	A
Aureolin Yellow – Pearlescent	10043	271	0	0.000	A	A	A	A
Purple Heart – Pearlescent	10044	271	0	0.000	A	A	A	A
Robin Egg – Pearlescent	10045	271	0	0.000	A	A	A	A
Fandango Pink – Pearlescent	10046	271	0	0.000	A	A	A	A
Scarlet – Pearlescent	10047	271	0	0.000	A	A	A	A
Gold – Pearlescent	10048	271	0	0.000	A	A	A	A
Lilac – Pearlescent	10049	271	0	0.000	A	A	A	A
Maya Blue – Pearlescent	10050	271	0	0.000	A	A	A	A
Electric Blue – Pearlescent	10051	271	0	0.000	A	A	A	A
Persian Green – Pearlescent	10052	271	0	0.000	A	A	A	A
Parakeet Green – Pearlescent	10053	271	0	0.000	A	A	A	A
Blue Heart – Pearlescent	10054	271	0	0.000	A	A	A	A
Borealis Pink – Pearlescent	10055	271	0	0.000	A	A	A	A
Celeste Blue – Pearlescent	10056	271	0	0.000	A	A	A	A
Double Peony – Pearlescent	10057	271	0	0.000	A	A	A	A
Egyptian Green – Pearlescent	10058	271	0	0.000	A	A	A	A
Graphite Black – Pearlescent	10059	271	0	0.000	A	A	A	A
Snowflake White – Pearlescent	10060	271	0	0.000	A	A	A	A
Purple Flame – Pearlescent	10061	271	0	0.000	A	A	A	A
Coral Rose – Pearlescent	10062	271	0	0.000	A	A	A	A
Olive Gold – Pearlescent	10063	271	0	0.000	A	A	A	A
Canary Yellow – Pearlescent	10064	271	0	0.000	A	A	A	A
Turmeric Gold – Pearlescent	10065	271	0	0.000	A	A	A	A
Silver Ash – Pearlescent	10066	271	0	0.000	A	A	A	A
UV Orange	10067	271	0	0.000	A	A	A	A
UV Lemon	10068	271	0	0.000	A	A	A	A
UV Pink	10069	271	0	0.000	A	A	A	A
UV Green	10070	271	0	0.000	A	A	A	A
UV Blue	10071	271	0	0.000	A	A	A	A
UV Purple	10072	271	0	0.000	A	A	A	A
UV Lime	10073	271	0	0.000	A	A	A	A
UV Red	10074	271	0	0.000	A	A	A	A
Flat Black	10075	271	0	0.000	A	A	A	A
Flat Red	10076	271	0	0.000	A	A	A	A
Flat White	10077	271	0	0.000	A	A	A	A
Flat Purple	10078	271	0	0.000	A	A	A	A
Flat Blue	10079	271	0	0.000	A	A	A	A
Flat Green	10080	271	0	0.000	A	A	A	A
Flat Cyan	10081	271	0	0.000	A	A	A	A
Flat Yellow	10082	271	0	0.000	A	A	A	A
None	10236	265	1	0.000	A	A	A	A
None	10287	266	1	0.000	A	A	A	A
Standard Base	10616	257	1	0.000	A	A	A	A
Solid - 1 color	13203	269	1	0.000	A	A	A	A
Burgundy - Pearlescent	10237	265	2	10.000	A	A	A	A
Burgundy - Pearlescent	10288	266	2	10.000	A	A	A	A
Suction Cup	10617	257	2	5.000	A	A	A	A
Marble - 2+ colors	13204	269	2	0.000	A	A	A	A
French Rose – Pearlescent	10238	265	3	10.000	A	A	A	A
French Rose – Pearlescent	10289	266	3	10.000	A	A	A	A
Vac-U-Lock Compatible	10618	257	3	5.000	A	A	A	A
Fade - 2+ colors	13205	269	3	0.000	A	A	A	A
Tiger Orange – Pearlescent	10239	265	4	10.000	A	A	A	A
Tiger Orange – Pearlescent	10290	266	4	10.000	A	A	A	A
Deep Pink – Pearlescent	10240	265	5	10.000	A	A	A	A
Deep Pink – Pearlescent	10291	266	5	10.000	A	A	A	A
Creamy Pink – Pearlescent	10241	265	6	10.000	A	A	A	A
Creamy Pink – Pearlescent	10292	266	6	10.000	A	A	A	A
Blue Iris – Pearlescent	10242	265	7	10.000	A	A	A	A
Blue Iris – Pearlescent	10293	266	7	10.000	A	A	A	A
Blue Du France – Pearlescent	10243	265	8	10.000	A	A	A	A
Blue Du France – Pearlescent	10294	266	8	10.000	A	A	A	A
Turquoise – Pearlescent	10244	265	9	10.000	A	A	A	A
Turquoise – Pearlescent	10295	266	9	10.000	A	A	A	A
Pearl White – Pearlescent	10245	265	10	10.000	A	A	A	A
Pearl White – Pearlescent	10296	266	10	10.000	A	A	A	A
Copper – Pearlescent	10246	265	11	10.000	A	A	A	A
Copper – Pearlescent	10297	266	11	10.000	A	A	A	A
Aureolin Yellow – Pearlescent	10247	265	12	10.000	A	A	A	A
Aureolin Yellow – Pearlescent	10298	266	12	10.000	A	A	A	A
Purple Heart – Pearlescent	10248	265	13	10.000	A	A	A	A
Purple Heart – Pearlescent	10299	266	13	10.000	A	A	A	A
Robin Egg – Pearlescent	10249	265	14	10.000	A	A	A	A
Robin Egg – Pearlescent	10300	266	14	10.000	A	A	A	A
Fandango Pink – Pearlescent	10250	265	15	10.000	A	A	A	A
Fandango Pink – Pearlescent	10301	266	15	10.000	A	A	A	A
Scarlet – Pearlescent	10251	265	16	10.000	A	A	A	A
Scarlet – Pearlescent	10302	266	16	10.000	A	A	A	A
Gold – Pearlescent	10252	265	17	10.000	A	A	A	A
Gold – Pearlescent	10303	266	17	10.000	A	A	A	A
Lilac – Pearlescent	10253	265	18	10.000	A	A	A	A
Lilac – Pearlescent	10304	266	18	10.000	A	A	A	A
Maya Blue – Pearlescent	10254	265	19	10.000	A	A	A	A
Maya Blue – Pearlescent	10305	266	19	10.000	A	A	A	A
Electric Blue – Pearlescent	10255	265	20	10.000	A	A	A	A
Electric Blue – Pearlescent	10306	266	20	10.000	A	A	A	A
Persian Green – Pearlescent	10256	265	21	10.000	A	A	A	A
Persian Green – Pearlescent	10307	266	21	10.000	A	A	A	A
Parakeet Green – Pearlescent	10257	265	22	10.000	A	A	A	A
Parakeet Green – Pearlescent	10308	266	22	10.000	A	A	A	A
Blue Heart – Pearlescent	10258	265	23	10.000	A	A	A	A
Blue Heart – Pearlescent	10309	266	23	10.000	A	A	A	A
Borealis Pink – Pearlescent	10259	265	24	10.000	A	A	A	A
Borealis Pink – Pearlescent	10310	266	24	10.000	A	A	A	A
Celeste Blue – Pearlescent	10260	265	25	10.000	A	A	A	A
Celeste Blue – Pearlescent	10311	266	25	10.000	A	A	A	A
Double Peony – Pearlescent	10261	265	26	10.000	A	A	A	A
Double Peony – Pearlescent	10312	266	26	10.000	A	A	A	A
Egyptian Green – Pearlescent	10262	265	27	10.000	A	A	A	A
Egyptian Green – Pearlescent	10313	266	27	10.000	A	A	A	A
Graphite Black – Pearlescent	10263	265	28	10.000	A	A	A	A
Graphite Black – Pearlescent	10314	266	28	10.000	A	A	A	A
Snowflake White – Pearlescent	10264	265	29	10.000	A	A	A	A
Snowflake White – Pearlescent	10315	266	29	10.000	A	A	A	A
Purple Flame – Pearlescent	10265	265	30	10.000	A	A	A	A
Purple Flame – Pearlescent	10316	266	30	10.000	A	A	A	A
Coral Rose – Pearlescent	10266	265	31	10.000	A	A	A	A
Coral Rose – Pearlescent	10317	266	31	10.000	A	A	A	A
Olive Gold – Pearlescent	10267	265	32	10.000	A	A	A	A
Olive Gold – Pearlescent	10318	266	32	10.000	A	A	A	A
Canary Yellow – Pearlescent	10268	265	33	10.000	A	A	A	A
Canary Yellow – Pearlescent	10319	266	33	10.000	A	A	A	A
Turmeric Gold – Pearlescent	10269	265	34	10.000	A	A	A	A
Turmeric Gold – Pearlescent	10320	266	34	10.000	A	A	A	A
Silver Ash – Pearlescent	10270	265	35	10.000	A	A	A	A
Silver Ash – Pearlescent	10321	266	35	10.000	A	A	A	A
UV Orange	10271	265	36	10.000	A	A	A	A
UV Orange	10322	266	36	10.000	A	A	A	A
UV Lemon	10272	265	37	10.000	A	A	A	A
UV Lemon	10323	266	37	10.000	A	A	A	A
UV Pink	10273	265	38	10.000	A	A	A	A
UV Pink	10324	266	38	10.000	A	A	A	A
UV Green	10274	265	39	10.000	A	A	A	A
UV Green	10325	266	39	10.000	A	A	A	A
UV Blue	10275	265	40	10.000	A	A	A	A
UV Blue	10326	266	40	10.000	A	A	A	A
UV Purple	10276	265	41	10.000	A	A	A	A
UV Purple	10327	266	41	10.000	A	A	A	A
UV Lime	10277	265	42	10.000	A	A	A	A
UV Lime	10328	266	42	10.000	A	A	A	A
UV Red	10278	265	43	10.000	A	A	A	A
UV Red	10329	266	43	10.000	A	A	A	A
Flat Black	10279	265	44	10.000	A	A	A	A
Flat Black	10330	266	44	10.000	A	A	A	A
Flat Red	10280	265	45	10.000	A	A	A	A
Flat Red	10331	266	45	10.000	A	A	A	A
Flat White	10281	265	46	10.000	A	A	A	A
Flat White	10332	266	46	10.000	A	A	A	A
Flat Purple	10282	265	47	10.000	A	A	A	A
Flat Purple	10333	266	47	10.000	A	A	A	A
Flat Blue	10283	265	48	10.000	A	A	A	A
Flat Blue	10334	266	48	10.000	A	A	A	A
Flat Green	10284	265	49	10.000	A	A	A	A
Flat Green	10335	266	49	10.000	A	A	A	A
Flat Cyan	10285	265	50	10.000	A	A	A	A
Flat Cyan	10336	266	50	10.000	A	A	A	A
Flat Yellow	10286	265	51	10.000	A	A	A	A
Flat Yellow	10337	266	51	10.000	A	A	A	A