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 (510, 1149, 535, 622, 850, 851) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00141

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 43 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
13248 510 10 0.000 A 0.000 A CK01 55x70 cm
14038 535 10 0.000 A 0.000 A 01 | 55x78 cm
17546 850 10 0.000 A 0.000 A FEP
13270 510 11 0.000 A 0.000 A C01 55x70 cm
13249 510 20 0.000 A 0.000 A CK02 55x78 cm
14039 535 20 0.000 A 0.000 A 02 | 55x98 cm
17547 850 20 0.000 A 0.000 A FKP / FKU
13271 510 21 0.000 A 0.000 A C02 55x78 cm
15309 535 22 0.000 A 0.000 A 16 | 55x118 cm
17551 851 22 0.000 A 0.000 A 66x98 cm
17552 851 24 0.000 A 0.000 A 66x118 cm
13250 510 30 0.000 A 0.000 A CK04 55x98 cm
14040 535 30 0.000 A 0.000 A 03 | 66x98 cm
13272 510 31 0.000 A 0.000 A C04 55x98 cm
17554 851 32 0.000 A 0.000 A 78x98 cm
17555 851 34 0.000 A 0.000 A 78x118 cm
17556 851 36 0.000 A 0.000 A 78x140 cm
13251 510 40 0.000 A 0.000 A CK06 55x118 cm
14041 535 40 0.000 A 0.000 A 04 | 66x118 cm
13273 510 41 0.000 A 0.000 A C06 55x118 cm
14215 535 42 0.000 A 0.000 A 14 | 66x140 cm
17558 851 42 0.000 A 0.000 A 94x118 cm
17559 851 44 0.000 A 0.000 A 94x140 cm
13252 510 50 0.000 A 0.000 A FK04 66x98 cm
14042 535 50 0.000 A 0.000 A 05 | 78x98 cm
13274 510 51 0.000 A 0.000 A F04 66x98 cm
17561 851 52 0.000 A 0.000 A 114x118 cm
17562 851 54 0.000 A 0.000 A 114x140 cm
13253 510 60 0.000 A 0.000 A FK06 66x118 cm
14043 535 60 0.000 A 0.000 A 06 | 78x118 cm
13275 510 61 0.000 A 0.000 A F06 66x118 cm
17563 851 62 0.000 A 0.000 A 134x98 cm
13254 510 70 0.000 A 0.000 A FK08 66x140 cm
14044 535 70 0.000 A 0.000 A 07 | 78x140 cm
13276 510 71 0.000 A 0.000 A F08 66x140 cm
14045 535 72 0.000 A 0.000 A 13 | 78x160 cm
13255 510 80 0.000 A 0.000 A MK04 78x98 cm
14046 535 80 0.000 A 0.000 A 08 | 94x118 cm
13277 510 81 0.000 A 0.000 A M04 78x98 cm
15153 622 89 0.000 A 0.000 A 089 | 10%
13256 510 90 0.000 A 0.000 A MK06 78x118 cm
14047 535 90 0.000 A 0.000 A 09 | 94x140 cm
15154 622 90 0.000 A 0.000 A 090 | 10%
13278 510 91 0.000 A 0.000 A M06 78x118 cm
15156 622 92 0.000 A 0.000 A 092 | 1%
15373 535 92 0.000 A 0.000 A 80 | 94x160 cm
13257 510 100 0.000 A 0.000 A MK08 78x140 cm
14048 535 100 0.000 A 0.000 A 10 | 114x118 cm
13279 510 101 0.000 A 0.000 A M08 78x140 cm
13258 510 110 0.000 A 0.000 A MK10 78x160 cm
14049 535 110 0.000 A 0.000 A 11 | 114x140 cm
13280 510 111 0.000 A 0.000 A M10 78x160 cm
13882 510 120 0.000 A 0.000 A MK12 78x180 cm
14050 535 120 0.000 A 0.000 A 12 | 134x98 cm
13260 510 130 0.000 A 0.000 A PK04 94x98 cm
13282 510 131 0.000 A 0.000 A P04 94x98 cm
13261 510 140 0.000 A 0.000 A PK06 94x118 cm
13283 510 141 0.000 A 0.000 A P06 94x118 cm
13262 510 150 0.000 A 0.000 A PK08 94x140 cm
13284 510 151 0.000 A 0.000 A P08 94x140 cm
13263 510 160 0.000 A 0.000 A PK10 94x160 cm
13285 510 161 0.000 A 0.000 A P10 94x160 cm
13264 510 170 0.000 A 0.000 A SK06 114x118 cm
13286 510 171 0.000 A 0.000 A S06 114x118 cm
13265 510 180 0.000 A 0.000 A SK08 114x140 cm
13287 510 181 0.000 A 0.000 A S08 114x140 cm
13266 510 190 0.000 A 0.000 A SK10 114x160 cm
13288 510 191 0.000 A 0.000 A S10 114x160 cm
13267 510 200 0.000 A 0.000 A UK04 134x98 cm
13289 510 201 0.000 A 0.000 A U04 134x98 cm
13268 510 210 0.000 A 0.000 A UK08 134x140 cm
13290 510 211 0.000 A 0.000 A U08 134x140 cm
13269 510 220 0.000 A 0.000 A UK10 134x160 cm
13291 510 221 0.000 A 0.000 A U10 134x160 cm