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 (1231, 1232, 1230, 1229, 1317, 1321) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00163

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 135 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
22930 1229 507 0.000 A 0.000 A 54x78 | WD → 60x84 (cm)
22934 1230 507 0.000 A 0.000 A 54x78 | WD → 60x84 (cm)
22966 1231 507 0.000 A 0.000 A 54x78 | WD → 60x84 (cm)
22984 1232 507 0.000 A 0.000 A 54x78 | WD → 60x84 (cm)
23928 1317 507 0.000 A 0.000 A 54x78
23984 1321 507 0.000 A 0.000 A 54x78
22926 1229 509 0.000 A 0.000 A 54x98 | WD → 60x104 (cm)
22935 1230 509 0.000 A 0.000 A 54x98 | WD → 60x104 (cm)
22962 1231 509 0.000 A 0.000 A 54x98 | WD → 60x104 (cm)
22980 1232 509 0.000 A 0.000 A 54x98 | WD → 60x104 (cm)
23929 1317 509 0.000 A 0.000 A 54x98
23983 1321 509 0.000 A 0.000 A 54x98
23973 1231 511 0.000 A 0.000 A 54x118 | WD → 60x124 (cm)
23976 1232 511 0.000 A 0.000 A 54x118 | WD → 60x124 (cm)
23991 1230 511 0.000 A 0.000 A 54x118 | WD → 60x124
23994 1229 511 0.000 A 0.000 A 54x118 | WD → 60x124
22920 1229 609 0.000 A 0.000 A 65x98 | WD → 71x104 (cm)
22937 1230 609 0.000 A 0.000 A 65x98 | WD → 71x104 (cm)
22956 1231 609 0.000 A 0.000 A 65x98 | WD → 71x104 (cm)
22974 1232 609 0.000 A 0.000 A 65x98 | WD → 71x104 (cm)
23930 1317 609 0.000 A 0.000 A 65x98
23981 1321 609 0.000 A 0.000 A 65x98
22933 1229 611 0.000 A 0.000 A 65x118 | WD → 71x124 (cm)
22938 1230 611 0.000 A 0.000 A 65x118 | WD → 71x124 (cm)
22969 1231 611 0.000 A 0.000 A 65x118 | WD → 71x124 (cm)
22987 1232 611 0.000 A 0.000 A 65x118 | WD → 71x124 (cm)
23931 1317 611 0.000 A 0.000 A 65x118
23980 1321 611 0.000 A 0.000 A 65x118
22932 1229 614 0.000 A 0.000 A 65x140 | WD → 71x146 (cm)
22939 1230 614 0.000 A 0.000 A 65x140 | WD → 71x146 (cm)
22968 1231 614 0.000 A 0.000 A 65x140 | WD → 71x146 (cm)
22986 1232 614 0.000 A 0.000 A 65x140 | WD → 71x146 (cm)
23932 1317 614 0.000 A 0.000 A 65x140
23982 1321 614 0.000 A 0.000 A 65x140
22931 1229 709 0.000 A 0.000 A 74x98 | WD → 80x104 (cm)
22940 1230 709 0.000 A 0.000 A 74x98 | WD → 80x104 (cm)
22967 1231 709 0.000 A 0.000 A 74x98 | WD → 80x104 (cm)
22985 1232 709 0.000 A 0.000 A 74x98 | WD → 80x104 (cm)
23933 1317 709 0.000 A 0.000 A 74x98
23979 1321 709 0.000 A 0.000 A 74x98
22929 1229 711 0.000 A 0.000 A 74x118 | WD → 80x124 (cm)
22941 1230 711 0.000 A 0.000 A 74x118 | WD → 80x124 (cm)
22965 1231 711 0.000 A 0.000 A 74x118 | WD → 80x124 (cm)
22983 1232 711 0.000 A 0.000 A 74x118 | WD → 80x124 (cm)
23934 1317 711 0.000 A 0.000 A 74x118
23990 1321 711 0.000 A 0.000 A 74x118
22928 1229 714 0.000 A 0.000 A 74x140 | WD → 80x146 (cm)
22942 1230 714 0.000 A 0.000 A 74x140 | WD → 80x146 (cm)
22964 1231 714 0.000 A 0.000 A 74x140 | WD → 80x146 (cm)
22982 1232 714 0.000 A 0.000 A 74x140 | WD → 80x146 (cm)
23935 1317 714 0.000 A 0.000 A 74x140
23989 1321 714 0.000 A 0.000 A 74x140
22963 1231 716 0.000 A 0.000 A 74x160 | WD → 80x166 (cm)
22981 1232 716 0.000 A 0.000 A 74x160 | WD → 80x166 (cm)
23974 1231 909 0.000 A 0.000 A 94x98 | WD → 100x104 (cm)
23977 1232 909 0.000 A 0.000 A 94x98 | WD → 100x104 (cm)
23992 1230 909 0.000 A 0.000 A 98x98 | | WD → 100x104 (cm)
23995 1229 909 0.000 A 0.000 A 98x98 | | WD → 100x104 (cm)
22924 1229 911 0.000 A 0.000 A 94x118 | WD → 100x124 (cm)
22944 1230 911 0.000 A 0.000 A 94x118 | WD → 100x124 (cm)
22960 1231 911 0.000 A 0.000 A 94x118 | WD → 100x124 (cm)
22978 1232 911 0.000 A 0.000 A 94x118 | WD → 100x124 (cm)
23937 1317 911 0.000 A 0.000 A 94x118
23988 1321 911 0.000 A 0.000 A 94x118
22923 1229 914 0.000 A 0.000 A 94x140 | WD → 100x146 (cm)
22945 1230 914 0.000 A 0.000 A 94x140 | WD → 100x146 (cm)
22959 1231 914 0.000 A 0.000 A 94x140 | WD → 100x146 (cm)
22977 1232 914 0.000 A 0.000 A 94x140 | WD → 100x146 (cm)
23938 1317 914 0.000 A 0.000 A 94x140
23987 1321 914 0.000 A 0.000 A 94x140
23975 1231 1109 0.000 A 0.000 A 114x98 | WD → 120x104 (cm)
23978 1232 1109 0.000 A 0.000 A 114x98 | WD → 120x104 (cm)
23993 1230 1109 0.000 A 0.000 A 114x98 | WD → 120x104 (cm)
23996 1229 1109 0.000 A 0.000 A 114x98 | WD → 120x104 (cm)
22922 1229 1111 0.000 A 0.000 A 114x118 | WD → 120x124 (cm)
22946 1230 1111 0.000 A 0.000 A 114x118 | WD → 120x124 (cm)
22958 1231 1111 0.000 A 0.000 A 114x118 | WD → 120x124 (cm)
22976 1232 1111 0.000 A 0.000 A 114x118 | WD → 120x124 (cm)
23939 1317 1111 0.000 A 0.000 A 114x118
23986 1321 1111 0.000 A 0.000 A 114x118
22921 1229 1114 0.000 A 0.000 A 114x140 | WD → 120x146 (cm)
22947 1230 1114 0.000 A 0.000 A 114x140 | WD → 120x146 (cm)
22957 1231 1114 0.000 A 0.000 A 114x140 | WD → 120x146 (cm)
22975 1232 1114 0.000 A 0.000 A 114x140 | WD → 120x146 (cm)
23940 1317 1114 0.000 A 0.000 A 114x140
23985 1321 1114 0.000 A 0.000 A 114x140