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 = 1 
WHERE 
  a.option_id IN (
    927, 690, 1164, 1159, 1155, 1149, 1323, 
    1322, 1081, 1082, 1167, 1168, 989
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00209

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 119 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
21886 1155 0 0.000 A 0.000 A Aluminium
24005 1323 0 0.000 A 0.000 A TFX | for tiles up to 50mm in profile
22062 1164 1 0.000 A 0.000 A 102
22063 1164 2 0.000 A 0.000 A 104
22064 1164 3 0.000 A 0.000 A 206
24007 1323 5 0.000 A 0.000 A LSX | for slate up to 16mm thick
22138 1167 10 0.000 A 0.000 A ZRH1
22142 1168 10 0.000 A 0.000 A White
22065 1164 11 0.000 A 0.000 A C01
22066 1164 12 0.000 A 0.000 A CK01
22139 1167 20 0.000 A 0.000 A ZRH12
22143 1168 20 0.000 A 0.000 A Black
22067 1164 21 0.000 A 0.000 A C02
22068 1164 22 0.000 A 0.000 A CK02
22140 1167 30 0.000 A 0.000 A ZRW7
22069 1164 31 0.000 A 0.000 A C04
22071 1164 32 0.000 A 0.000 A CK04
22070 1164 33 0.000 A 0.000 A C06
22072 1164 34 0.000 A 0.000 A CK06
22141 1167 40 0.000 A 0.000 A ZRS24
22073 1164 41 0.000 A 0.000 A F04
22074 1164 42 0.000 A 0.000 A FK04
21887 1155 50 0.000 A 0.000 A White
24009 1323 50 0.000 A 0.000 A UCX13 | for kombi - 2 windows side by side
22075 1164 51 0.000 A 0.000 A F06
22076 1164 52 0.000 A 0.000 A FK06
22077 1164 61 0.000 A 0.000 A F08
22078 1164 62 0.000 A 0.000 A FK08
22079 1164 71 0.000 A 0.000 A M04
22080 1164 72 0.000 A 0.000 A MK04
22081 1164 81 0.000 A 0.000 A M06
22082 1164 82 0.000 A 0.000 A MK06
22083 1164 91 0.000 A 0.000 A M08
22084 1164 92 0.000 A 0.000 A MK08
18185 927 100 0.000 A 0.000 A Roof window (handle at the bottom of the sash)
22085 1164 101 0.000 A 0.000 A M10
15718 690 102 0.000 A 0.000 A CK02 55x78 cm
22086 1164 102 0.000 A 0.000 A MK10
15719 690 104 0.000 A 0.000 A CK04 55x98 cm
15720 690 106 0.000 A 0.000 A CK06 55x118 cm
20530 1082 110 0.000 A 0.000 A Thermo | for tiled coverings (up to 90mm height)
22087 1164 111 0.000 A 0.000 A M12
22088 1164 112 0.000 A 0.000 A MK12
23997 1322 120 0.000 A 0.000 A C2A | 55x78 cm
22089 1164 121 0.000 A 0.000 A M31
22090 1164 131 0.000 A 0.000 A M50
23998 1322 140 0.000 A 0.000 A C4A | 55x98 cm
22091 1164 141 0.000 A 0.000 A P04
22092 1164 142 0.000 A 0.000 A PK04
22093 1164 151 0.000 A 0.000 A P06
22094 1164 152 0.000 A 0.000 A PK06
22095 1164 161 0.000 A 0.000 A P08
22096 1164 162 0.000 A 0.000 A PK08
22097 1164 171 0.000 A 0.000 A P10
22098 1164 172 0.000 A 0.000 A PK10
22099 1164 181 0.000 A 0.000 A P31
22100 1164 191 0.000 A 0.000 A P50
18186 927 200 0.000 A 0.000 A Roof access window (handle on left side of sash)
22101 1164 201 0.000 A 0.000 A S04
15721 690 204 0.000 A 0.000 A FK04 66x98 cm
15722 690 206 0.000 A 0.000 A FK06 66x118 cm
15723 690 208 0.000 A 0.000 A FK08 66x140 cm
22102 1164 211 0.000 A 0.000 A S06
22103 1164 221 0.000 A 0.000 A S08
22104 1164 221 0.000 A 0.000 A SK06
22105 1164 222 0.000 A 0.000 A SK08
22106 1164 231 0.000 A 0.000 A S10
22107 1164 232 0.000 A 0.000 A SK10
22108 1164 241 0.000 A 0.000 A S31
22109 1164 251 0.000 A 0.000 A S50
22110 1164 261 0.000 A 0.000 A U04
22111 1164 262 0.000 A 0.000 A UK04
22112 1164 271 0.000 A 0.000 A U08
22113 1164 272 0.000 A 0.000 A UK08
22114 1164 281 0.000 A 0.000 A U10
22115 1164 282 0.000 A 0.000 A UK10
22116 1164 291 0.000 A 0.000 A U31
18187 927 300 0.000 A 0.000 A Roof access window (handle on right side of sash)
22117 1164 301 0.000 A 0.000 A U50
15724 690 304 0.000 A 0.000 A MK04 78x98 cm
15725 690 306 0.000 A 0.000 A MK06 78x118 cm
15726 690 308 0.000 A 0.000 A MK08 78x140 cm
15727 690 310 0.000 A 0.000 A MK10 78x160 cm
15728 690 312 0.000 A 0.000 A MK12 78x180 cm
23999 1322 360 0.000 A 0.000 A F6A | 66x118 cm
15729 690 404 0.000 A 0.000 A PK04 94x98 cm
15730 690 406 0.000 A 0.000 A PK06 94x118 cm
15731 690 408 0.000 A 0.000 A PK08 94x140 cm
15732 690 410 0.000 A 0.000 A PK10 94x160 cm
15733 690 506 0.000 A 0.000 A SK06 114x118 cm
19567 989 507 0.000 A 0.000 A C2A
15734 690 508 0.000 A 0.000 A SK08 114x140 cm
19568 989 509 0.000 A 0.000 A C4A
20506 1081 509 0.000 A 0.000 A 02 | 55x98 cm
15735 690 510 0.000 A 0.000 A SK10 114x160 cm
20507 1081 511 0.000 A 0.000 A 16 | 55x118 cm
24000 1322 540 0.000 A 0.000 A M4A | 78x98 cm
24001 1322 560 0.000 A 0.000 A M6A | 78x118 cm
24002 1322 580 0.000 A 0.000 A M8A | 78x140 cm
15736 690 604 0.000 A 0.000 A UK04 134x98 cm
15737 690 608 0.000 A 0.000 A UK08 134x140 cm
20508 1081 609 0.000 A 0.000 A 03 | 66x98 cm
15738 690 610 0.000 A 0.000 A UK10 134x160 cm
19570 989 611 0.000 A 0.000 A F6A
20509 1081 611 0.000 A 0.000 A 04 | 66x118 cm
20510 1081 614 0.000 A 0.000 A 14 | 66x140 cm
19562 989 709 0.000 A 0.000 A M4A
20511 1081 709 0.000 A 0.000 A 05 | 78x98 cm
19561 989 711 0.000 A 0.000 A M6A
20512 1081 711 0.000 A 0.000 A 06 | 78x118 cm
19571 989 714 0.000 A 0.000 A M8A
20513 1081 714 0.000 A 0.000 A 07 | 78x140 cm
20514 1081 716 0.000 A 0.000 A 13 | 78x160 cm
20515 1081 718 0.000 A 0.000 A 40 | 78x180 cm
24003 1322 760 0.000 A 0.000 A S6A | 114x118 cm
20516 1081 909 0.000 A 0.000 A 15 | 94x98 cm
20517 1081 911 0.000 A 0.000 A 08 | 94x118 cm
20518 1081 914 0.000 A 0.000 A 09 | 94x140 cm
20519 1081 916 0.000 A 0.000 A 80 | 94x160 cm
20520 1081 918 0.000 A 0.000 A 41 | 94x180 cm
21897 1159 1028 0.000 A 0.000 A 1028 | White
21898 1159 1086 0.000 A 0.000 A 1086 | Beige
20521 1081 1109 0.000 A 0.000 A 20 | 114x98 cm
19565 989 1111 0.000 A 0.000 A S6A
20522 1081 1111 0.000 A 0.000 A 10 | 114x118 cm
20523 1081 1114 0.000 A 0.000 A 11 | 114x140 cm
20524 1081 1116 0.000 A 0.000 A 50 | 114x160 cm
20525 1081 1309 0.000 A 0.000 A 12 | 134x98 cm
20526 1081 1311 0.000 A 0.000 A 18 | 134x118 cm
20527 1081 1314 0.000 A 0.000 A 17 | 134x140 cm
21900 1159 4069 0.000 A 0.000 A 4069 | Black
21901 1159 4155 0.000 A 0.000 A 4155 | Sand
21896 1159 4161 0.000 A 0.000 A 4161 | Grey
21902 1159 4162 0.000 A 0.000 A 4162 | Dark brown
21904 1159 4163 0.000 A 0.000 A 4163 | Warm Grey
21903 1159 4164 0.000 A 0.000 A 4164 | Orange
21905 1159 4165 0.000 A 0.000 A 4165 | Dusty Green
21906 1159 4166 0.000 A 0.000 A 4166 | Light Blue
21907 1159 4167 0.000 A 0.000 A 4167 | Taupe
21908 1159 4168 0.000 A 0.000 A 4168 | Soft Rose
21910 1159 4169 0.000 A 0.000 A 4169 | Light Taupe
21911 1159 4170 0.000 A 0.000 A 4170 | Dark Petrol
21909 1159 4171 0.000 A 0.000 A 4171 | Natural
21899 1159 9050 0.000 A 0.000 A 9050 | Dark Blue