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, 93, 92, 91, 65, 66, 67, 84, 85) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00112

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "21.00"
    },
    "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": 45,
            "filtered": "33.74",
            "cost_info": {
              "read_cost": "2.81",
              "eval_cost": "4.59",
              "prefix_cost": "16.41",
              "data_read_per_join": "36K"
            },
            "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,93,92,91,65,66,67,84,85))"
          }
        },
        {
          "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": 45,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "4.59",
              "prefix_cost": "21.00",
              "data_read_per_join": "35K"
            },
            "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
NPT	913	93	0	0.000	A	A	A	A
Tube fitting	914	93	0	0.000	A	A	A	A
1/4 F	915	92	0	0.000	A	A	A	A
1/4 M	916	92	0	0.000	A	A	A	A
4 mm	917	92	0	0.000	A	A	A	A
6 mm	918	92	0	0.000	A	A	A	A
8 mm	919	92	0	0.000	A	A	A	A
12 mm	920	92	0	0.000	A	A	A	A
1/4"	921	92	0	0.000	A	A	A	A
1/8"	922	92	0	0.000	A	A	A	A
3/8"	923	92	0	0.000	A	A	A	A
1/2"	924	92	0	0.000	A	A	A	A
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
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
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
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
1/4 M	891	65	40	0.000	A	A	A	A
4 mm	904	65	170	0.000	A	A	A	A
6 mm	905	65	180	0.000	A	A	A	A
8 mm	906	65	190	0.000	A	A	A	A
12 mm	908	65	210	0.000	A	A	A	A
1/4"	909	65	220	0.000	A	A	A	A
3/8"	910	65	230	0.000	A	A	A	A
1/2"	911	65	240	0.000	A	A	A	A
1/8"	912	65	250	0.000	A	A	A	A