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 (861, 857, 862, 863, 858, 865, 860) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00163

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "190.76"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "105.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": 105,
            "rows_produced_per_join": 105,
            "filtered": "100.00",
            "index_condition": "((`cscartdb`.`a`.`option_id` in (861,857,862,863,858,865,860)) and (`cscartdb`.`a`.`status` = 'A'))",
            "cost_info": {
              "read_cost": "38.51",
              "eval_cost": "10.50",
              "prefix_cost": "49.01",
              "data_read_per_join": "4K"
            },
            "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": 105,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "26.25",
              "eval_cost": "10.50",
              "prefix_cost": "85.76",
              "data_read_per_join": "80K"
            },
            "used_columns": [
              "variant_id",
              "lang_code",
              "variant_name"
            ]
          }
        }
      ]
    }
  }
}

Result

variant_name	variant_id	option_id	position	modifier_type	weight_modifier_type	point_modifier_type	status
Flesh-colored beige	16463	857	1	A	A	A	A
Smaller	16484	858	1	A	A	A	A
None	16617	863	1	A	A	A	A
None	16657	862	1	A	A	A	A
XS	16701	860	1	A	A	A	A
Black	16708	865	1	A	A	A	A
One-colored	17172	861	1	A	A	A	A
Flesh-colored pink	16464	857	2	A	A	A	A
Bigger	16485	858	2	A	A	A	A
Flesh-colored beige	16618	863	2	A	A	A	A
Flesh-colored beige	16658	862	2	A	A	A	A
S	16702	860	2	A	A	A	A
Rainbow Braid	16709	865	2	A	A	A	A
Double-colored	17173	861	2	A	A	A	A
Fleshy brown dark	16465	857	3	A	A	A	A
Flesh-colored pink	16619	863	3	A	A	A	A
Flesh-colored pink	16659	862	3	A	A	A	A
M	16703	860	3	A	A	A	A
Magic World	16710	865	3	A	A	A	A
Tri-colored	17174	861	3	A	A	A	A
Fleshy brown light	16466	857	4	A	A	A	A
Fleshy brown dark	16620	863	4	A	A	A	A
Fleshy brown dark	16660	862	4	A	A	A	A
L	16704	860	4	A	A	A	A
Skulls & Roses	16711	865	4	A	A	A	A
Light Blue	16467	857	5	A	A	A	A
Fleshy brown light	16621	863	5	A	A	A	A
Fleshy brown light	16661	862	5	A	A	A	A
XL	16705	860	5	A	A	A	A
Light Rose	16468	857	6	A	A	A	A
Light Blue	16622	863	6	A	A	A	A
Light Blue	16662	862	6	A	A	A	A
2XL	16706	860	6	A	A	A	A
Black	16469	857	7	A	A	A	A
Light Rose	16623	863	7	A	A	A	A
Light Rose	16663	862	7	A	A	A	A
3XL	16707	860	7	A	A	A	A
Blue	16470	857	8	A	A	A	A
Lavender	16624	863	8	A	A	A	A
Lavender	16664	862	8	A	A	A	A
Brown	16471	857	9	A	A	A	A
Black	16625	863	9	A	A	A	A
Black	16665	862	9	A	A	A	A
Clear	16472	857	10	A	A	A	A
Brown	16626	863	10	A	A	A	A
BLue	16666	862	10	A	A	A	A
Gray	16473	857	11	A	A	A	A
Clear	16627	863	11	A	A	A	A
Brown	16667	862	11	A	A	A	A
Green	16474	857	12	A	A	A	A
Blue	16628	863	12	A	A	A	A
Clear	16668	862	12	A	A	A	A
Orange	16475	857	13	A	A	A	A
Gray	16629	863	13	A	A	A	A
Gray	16669	862	13	A	A	A	A
Pink	16476	857	14	A	A	A	A
Green	16630	863	14	A	A	A	A
Green	16670	862	14	A	A	A	A
Purple	16477	857	15	A	A	A	A
Orange	16631	863	15	A	A	A	A
Orange	16671	862	15	A	A	A	A
Yellow	16478	857	16	A	A	A	A
Pink	16632	863	16	A	A	A	A
Pink	16672	862	16	A	A	A	A
Red	16479	857	17	A	A	A	A
Purple	16633	863	17	A	A	A	A
Purple	16673	862	17	A	A	A	A
White	16480	857	18	A	A	A	A
Red	16634	863	18	A	A	A	A
Red	16674	862	18	A	A	A	A
Lavender	16481	857	19	A	A	A	A
White	16635	863	19	A	A	A	A
White	16675	862	19	A	A	A	A
Yellow	16636	863	20	A	A	A	A
Yellow	16676	862	20	A	A	A	A