Sandbox

SELECT 
  a.variant_id, 
  a.option_id, 
  a.position, 
  a.modifier, 
  a.modifier_type, 
  a.weight_modifier, 
  a.weight_modifier_type, 
  b.variant_name, 
  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 a.variant_id = b.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 = 2 
WHERE 
  a.option_id IN (1218, 658, 178) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00061

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	range	status,option_id,option_id_2	option_id	6		13	Using index condition; Using filesort
1	SIMPLE	b	eq_ref	PRIMARY	PRIMARY	9	mahm3t_cs443.a.variant_id,const	1	Using where
1	SIMPLE	shared_option_variants	eq_ref	PRIMARY,company_id	PRIMARY	7	mahm3t_cs443.a.variant_id,const	1

Result

variant_id	option_id	position	modifier	modifier_type	weight_modifier	weight_modifier_type	variant_name
15505	658	4	24.000	A	12.000	A	40 cm
15506	658	6	36.000	A	18.000	A	60 cm
15507	658	8	48.000	A	24.000	A	80 cm
4240	178	10	0.000	A	0.000	A	RAL 8004 - brick red
15508	658	10	60.000	A	30.000	A	100 cm
22769	1218	10	0.000	A	0.000	A	brick red (RAL 8004)
15509	658	12	72.000	A	36.000	A	120 cm
15510	658	15	90.000	A	45.000	A	150 cm
4241	178	20	0.000	A	0.000	A	RAL 8017 - brown
15511	658	20	120.000	A	60.000	A	200 cm
22770	1218	20	0.000	A	0.000	A	brown (RAL 8017)
4242	178	30	0.000	A	0.000	A	RAL 8019 - dark brown
22771	1218	30	0.000	A	0.000	A	dark brown (RAL 8019)
4243	178	40	0.000	A	0.000	A	RAL 7016 - graphite
22772	1218	40	0.000	A	0.000	A	red brown (RAL 8012)
4244	178	50	0.000	A	0.000	A	RAL 9005 - black
22773	1218	50	0.000	A	0.000	A	red (RAL 3004)
4245	178	60	0.000	A	0.000	A	RAL 3011 - red
22774	1218	60	0.000	A	0.000	A	black (RAL 9005)
4246	178	70	0.000	A	0.000	A	RAL 8012 - red brown
22775	1218	70	0.000	A	0.000	A	graphite (RAL 7016)
22776	1218	80	0.000	A	0.000	A	green (RAL 6020)