Sandbox

SELECT 
  b.variant_name, 
  a.*, 
  IF(
    shared_option_variants.variant_id IS NOT NULL, 
    shared_option_variants.modifier, 
    a.modifier
  ) as modifier, 
  IF(
    shared_option_variants.variant_id IS NOT NULL, 
    shared_option_variants.modifier_type, 
    a.modifier_type
  ) as modifier_type 
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' 
  LEFT JOIN cscart_ult_product_option_variants shared_option_variants ON shared_option_variants.variant_id = a.variant_id 
  AND shared_option_variants.company_id = 1 
WHERE 
  1 
  AND a.option_id IN (83, 90, 64, 91, 65, 66, 84, 85, 67) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00115

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.84"
    },
    "ordering_operation": {
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "shared_option_variants",
            "access_type": "system",
            "possible_keys": [
              "PRIMARY",
              "company_id"
            ],
            "rows_examined_per_scan": 0,
            "rows_produced_per_join": 1,
            "filtered": "0.00",
            "const_row_not_found": true,
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "24"
            },
            "used_columns": [
              "variant_id",
              "company_id",
              "modifier",
              "modifier_type"
            ]
          }
        },
        {
          "table": {
            "table_name": "a",
            "access_type": "ref",
            "possible_keys": [
              "status",
              "option_id",
              "option_id_2"
            ],
            "key": "status",
            "used_key_parts": [
              "status"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 136,
            "rows_produced_per_join": 94,
            "filtered": "69.33",
            "cost_info": {
              "read_cost": "2.81",
              "eval_cost": "9.43",
              "prefix_cost": "16.41",
              "data_read_per_join": "74K"
            },
            "used_columns": [
              "variant_id",
              "option_id",
              "position",
              "modifier",
              "modifier_type",
              "weight_modifier",
              "weight_modifier_type",
              "point_modifier",
              "point_modifier_type",
              "status",
              "yml2_variant"
            ],
            "attached_condition": "(`test2_gaseus`.`a`.`option_id` in (83,90,64,91,65,66,84,85,67))"
          }
        },
        {
          "table": {
            "table_name": "b",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "test2_gaseus.a.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 94,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "9.43",
              "prefix_cost": "25.84",
              "data_read_per_join": "72K"
            },
            "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
None	463	66	10	0.000	A	A	A	A
None	466	67	10	0.000	A	A	A	A
Wrench nut	506	83	10	0.000	A	A	A	A
Gauge	508	84	10	0.000	A	A	A	A
Gauge	511	85	10	0.000	A	A	A	A
DIN 477	814	90	10	0.000	A	A	A	A
DIN 477-1 No.001 (W 21.8 x 1/14 LH)	820	64	10	0.000	A	A	A	A
NPT	885	91	10	0.000	A	A	A	A
Self-purge	464	66	20	20.000	A	A	A	A
Handwheel	507	83	20	0.000	A	A	A	A
Contact gauge	509	84	20	0.000	A	A	A	A
Contact gauge	512	85	20	0.000	A	A	A	A
Ball valve	808	67	20	25.000	A	A	A	A
CGA	815	90	20	0.000	A	A	A	A
DIN 477-1 No.006 (W 21.8 x 1/14 RH)	821	64	20	0.000	A	A	A	A
Tube fitting	887	91	20	0.000	A	A	A	A
External purge	465	66	30	35.000	A	A	A	A
Pressure transmitter	510	84	30	0.000	A	A	A	A
Pressure transmitter	513	85	30	0.000	A	A	A	A
Diaphragm valve	809	67	30	40.000	A	A	A	A
BS	816	90	30	0.000	A	A	A	A
DIN 477-1 No.007 (G 5/8" RH)	822	64	30	0.000	A	A	A	A
1/4 F	890	65	30	0.000	A	A	A	A
Flow meter	514	85	40	0.000	A	A	A	A
Needle valve	810	67	40	15.000	A	A	A	A
NFE	817	90	40	0.000	A	A	A	A
DIN 477-1 No.014 (M 19 x 1.5 LH)	823	64	40	0.000	A	A	A	A
1/4 M	891	65	40	0.000	A	A	A	A
NEN	818	90	50	0.000	A	A	A	A
DIN 477-1 No.015 (M 19 x 1.5 RH)	824	64	50	0.000	A	A	A	A
UNI	819	90	60	0.000	A	A	A	A
DIN 477-1 No.017 (M 30 x 2 RH)	825	64	60	0.000	A	A	A	A
DIN 477-1 No.021 (G 3/4" RH)	826	64	70	0.000	A	A	A	A
DIN 477-1 No.024 (G 1/2" RH)	827	64	80	0.000	A	A	A	A
DIN 477-1 No.025 (G 3/8" RH)	828	64	90	0.000	A	A	A	A
DIN 477-1 No.028 (G 1/4" RH)	829	64	100	0.000	A	A	A	A
DIN 477-1 No.032 (G 5/8" LH)	830	64	110	0.000	A	A	A	A
DIN 477-1 No.033 (G 3/4" LH)	831	64	120	0.000	A	A	A	A
DIN 477-1 No.037 (M 14 x 1.5 LH)	832	64	130	0.000	A	A	A	A
DIN 477-1 No.040 (M 14 x 1.5 RH)	833	64	140	0.000	A	A	A	A
DIN 477-1 No.043 (M 18 x 1.5 LH)	834	64	150	0.000	A	A	A	A
DIN 477-1 No.044 (M 18 x 1.5 RH)	835	64	160	0.000	A	A	A	A
DIN 477-1 No.050 (M 20 x 1.5 RH)	836	64	170	0.000	A	A	A	A
4 mm	904	65	170	0.000	A	A	A	A
DIN 477-1 No.051 (M 20 x 1.5 LH)	837	64	180	0.000	A	A	A	A
6 mm	905	65	180	0.000	A	A	A	A
DIN 477-1 No.056 (M 22 x 1.5 RH)	838	64	190	0.000	A	A	A	A
8 mm	906	65	190	0.000	A	A	A	A
DIN 477-1 No.057 (M 22 x 1.5 LH)	839	64	200	0.000	A	A	A	A
DIN 477-1 No.061 (M 26 x 1.5 RH)	840	64	210	0.000	A	A	A	A
12 mm	908	65	210	0.000	A	A	A	A
DIN 477-1 No.062 (M 26 x 1.5 LH)	841	64	220	0.000	A	A	A	A
1/4"	909	65	220	0.000	A	A	A	A
DIN 477-1 No.063 (W 30 x 2 RH)	842	64	230	0.000	A	A	A	A
3/8"	910	65	230	0.000	A	A	A	A
DIN 477-1 No.064 (W 30 x 2 LH)	843	64	240	0.000	A	A	A	A
1/2"	911	65	240	0.000	A	A	A	A
CGA 320	857	64	250	0.000	A	A	A	A
1/8"	912	65	250	0.000	A	A	A	A
CGA 326	858	64	260	0.000	A	A	A	A
CGA 330	859	64	270	0.000	A	A	A	A
CGA 346	860	64	280	0.000	A	A	A	A
CGA 350	861	64	290	0.000	A	A	A	A
CGA 510	862	64	300	0.000	A	A	A	A
CGA 540	863	64	310	0.000	A	A	A	A
CGA 580	864	64	320	0.000	A	A	A	A
CGA 590	865	64	330	0.000	A	A	A	A
CGA 600	866	64	340	0.000	A	A	A	A
CGA 660	867	64	350	0.000	A	A	A	A
CGA 680	868	64	360	0.000	A	A	A	A
CGA 695	869	64	370	0.000	A	A	A	A
CGA 770	870	64	380	0.000	A	A	A	A
CGA 870	871	64	390	0.000	A	A	A	A
BS341 No.1	844	64	400	0.000	A	A	A	A
BS341 No.2	845	64	410	0.000	A	A	A	A
BS341 No.3	846	64	420	0.000	A	A	A	A
BS341 No.4	847	64	430	0.000	A	A	A	A
BS341 No.5	848	64	440	0.000	A	A	A	A
BS341 No.6	849	64	450	0.000	A	A	A	A
BS341 No.8	850	64	460	0.000	A	A	A	A
BS341 No.9	851	64	470	0.000	A	A	A	A
BS341 No.10	852	64	480	0.000	A	A	A	A
BS341 No.12	853	64	490	0.000	A	A	A	A
BS341 No.13	854	64	500	0.000	A	A	A	A
BS341 No.14	855	64	510	0.000	A	A	A	A
BS341 No.15	856	64	520	0.000	A	A	A	A
NFE 29-650	872	64	530	0.000	A	A	A	A
NFE 29-651	873	64	540	0.000	A	A	A	A
NFE 29-652	874	64	550	0.000	A	A	A	A
NFE 29-653	875	64	560	0.000	A	A	A	A
NFE 29-654	876	64	570	0.000	A	A	A	A
NFE 29-655	877	64	580	0.000	A	A	A	A
NEN 3268	878	64	590	0.000	A	A	A	A
NEN 3269	879	64	600	0.000	A	A	A	A
NEN 3270	880	64	610	0.000	A	A	A	A
UNI 4414	881	64	620	0.000	A	A	A	A
UNI 4415	882	64	630	0.000	A	A	A	A
UNI 11144	883	64	640	0.000	A	A	A	A
UNI 11145	884	64	650	0.000	A	A	A	A