SELECT 
  a.variant_id, 
  a.option_id, 
  a.position, 
  a.modifier, 
  a.modifier_type, 
  a.weight_modifier, 
  a.weight_modifier_type, 
  a.nav_skus, 
  a.commercial_modifier, 
  a.commercial_dn, 
  a.same_as_option, 
  a.price_overrides, 
  a.base_cost, 
  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 (
    22626, 23191, 23303, 22693, 22697, 22698, 
    23304, 21941, 22247
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00202

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "shared_option_variants",
      "access_type": "system",
      "possible_keys": ["PRIMARY", "company_id"],
      "rows": 0,
      "filtered": 0,
      "const_row_not_found": true
    },
    "read_sorted_file": {
      "filesort": {
        "sort_key": "a.position, a.variant_id",
        "table": {
          "table_name": "a",
          "access_type": "range",
          "possible_keys": ["option_id", "option_id_2", "status"],
          "key": "option_id",
          "key_length": "6",
          "used_key_parts": ["option_id", "status"],
          "rows": 141,
          "filtered": 100,
          "index_condition": "a.option_id in (22626,23191,23303,22693,22697,22698,23304,21941,22247) and a.`status` = 'A'"
        }
      }
    },
    "table": {
      "table_name": "b",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "9",
      "used_key_parts": ["variant_id", "lang_code"],
      "ref": ["cscart_migrate.a.variant_id", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(b.lang_code = 'en')"
    }
  }
}

Result

variant_id option_id position modifier modifier_type weight_modifier weight_modifier_type nav_skus commercial_modifier commercial_dn same_as_option price_overrides base_cost variant_name
220318 21941 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220320 22247 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
223480 22626 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
224688 22693 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
224768 22697 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
224773 22698 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
231994 23191 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
233199 23303 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
233200 23304 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
215376 21941 3 0.000 A 0.000 A E26 0.00 0.00 0 0.00 E26
219237 22247 3 0.000 A 0.000 A 0.00 0.00 0 0.00 None
223476 22626 3 0.000 A 0.000 A FLT10 0.00 0.00 0 0.00 10" Shade
224657 22693 3 0.000 A 0.000 A 0.00 0.00 23191 0.00 Same as Shade
224764 22697 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
224769 22698 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
231959 23191 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
233170 23303 3 11.000 A 0.000 A G26 0.00 0.00 0 0.00 G26 Gooseneck Arm
215377 21941 5 14.000 A 0.000 A GU24 0.00 0.00 0 3.50 GU24
219238 22247 5 19.000 A 0.000 A 19.00 0.00 0 0.00 LED Silver Bowl G25 - 31-4.5FG25DIM/827/SB/R
223477 22626 5 13.000 A 0.000 A FLT12 0.00 0.00 0 0.00 12" Shade
224658 22693 5 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
224767 22697 5 110.000 A 0.000 A DCHX 0.00 0.00 0 0.00 Copper BP & Hex Cover
224772 22698 5 121.000 A 0.000 A DCHX 0.00 0.00 0 0.00 Brass BP & Hex Cover
231960 23191 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
233171 23303 5 12.000 A 0.000 A G11 0.00 0.00 0 0.00 G11 Gooseneck Arm
233201 23304 5 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
219239 22247 10 13.000 A 0.000 A 13.00 0.00 0 0.00 LED Clear ST19 - 31-4.5FSST19DIM/821
224659 22693 10 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
224765 22697 10 202.000 A 0.000 A DD 0.00 0.00 0 0.00 Dusk-to-Dawn Photocell
224770 22698 10 213.000 A 0.000 A DD 0.00 0.00 0 0.00 Dusk-to-Dawn Photocell
231961 23191 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
233172 23303 10 15.000 A 0.000 A G15 0.00 0.00 0 0.00 G15 Gooseneck Arm
233202 23304 10 39.000 A 0.000 A DBPC 0.00 0.00 0 0.00 Decorative BP Cover
233538 23304 12 61.000 A 0.000 A DCHX 0.00 0.00 0 0.00 Decorative BP & Hex Cover
224660 22693 15 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
224766 22697 15 83.000 A 0.000 A HDBP 0.00 0.00 0 0.00 Heavy Duty BP
224771 22698 15 83.000 A 0.000 A HDBP 0.00 0.00 0 0.00 Heavy Duty BP
231962 23191 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
233173 23303 15 17.000 A 0.000 A G22 0.00 0.00 0 0.00 G22 Gooseneck Arm
233203 23304 15 92.000 A 0.000 A DD 0.00 0.00 0 0.00 Dusk-to-Dawn Photocell
242973 22247 15 13.000 A 0.000 A 13.00 0.00 0 0.00 LED Amber ST19 - 31-4.5FSST19DIM/820/A
233193 23303 18 14.000 A 0.000 A G8 0.00 0.00 0 0.00 G8 Gooseneck Arm
219241 22247 20 14.000 A 0.000 A 14.00 0.00 0 0.00 LED Smoky ST19 - 31-4.5FSST19DIM/840/S
224661 22693 20 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
231963 23191 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
233174 23303 20 26.000 A 0.000 A G24 0.00 0.00 0 0.00 G24 Gooseneck Arm
233204 23304 20 83.000 A 0.000 A HDBP 0.00 0.00 0 0.00 Heavy Duty BP
224662 22693 25 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
231964 23191 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Rust
233175 23303 25 26.000 A 0.000 A G6 0.00 0.00 0 0.00 G6 Gooseneck Arm
237094 23191 27 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
224682 22693 28 0.000 A 0.000 A 980 0.00 0.00 0 0.00 980-Brushed Aluminum
237095 23191 28 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
224663 22693 30 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Rust
231965 23191 30 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
233176 23303 30 0.000 A 0.000 A G3 0.00 0.00 0 0.00 G3 Straight Arm
237052 22693 32 11.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237053 22693 33 11.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
224664 22693 35 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
231966 23191 35 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
233177 23303 35 0.000 A 0.000 A G4 0.00 0.00 0 0.00 G4 Straight Arm
224665 22693 40 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
231967 23191 40 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
233178 23303 40 4.000 A 0.000 A G1 0.00 0.00 0 0.00 G1 Straight Arm
224666 22693 45 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
231968 23191 45 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
233179 23303 45 4.000 A 0.000 A G14 0.00 0.00 0 0.00 G14 Straight Arm
224667 22693 50 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
231969 23191 50 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
233180 23303 50 2.000 A 0.000 A G16 0.00 0.00 0 0.00 G16 Straight Arm
224668 22693 55 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
231970 23191 55 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
233181 23303 55 2.000 A 0.000 A G17 0.00 0.00 0 0.00 G17 Straight Arm
219249 22247 60 13.000 A 0.000 A 13.00 0.00 0 0.00 Lurra LED Tala Lamp - 28LURA
224669 22693 60 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
231971 23191 60 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
233182 23303 60 26.000 A 0.000 A G35 0.00 0.00 0 0.00 G35 Gooseneck Arm
219250 22247 65 15.000 A 0.000 A 15.00 0.00 0 0.00 Globe LED Tala Lamp - 28GLOB
224670 22693 65 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
231972 23191 65 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
233183 23303 65 30.000 A 0.000 A G34 0.00 0.00 0 0.00 G34 Gooseneck Arm
224671 22693 70 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
231973 23191 70 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
233184 23303 70 16.000 A 0.000 A G5 0.00 0.00 0 0.00 G5 Gooseneck Arm
219252 22247 75 28.000 A 0.000 A 28.00 0.00 0 0.00 Elva LED Tala Lamp - 28ELVA
224672 22693 75 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
231974 23191 75 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
233185 23303 75 21.000 A 0.000 A G25 0.00 0.00 0 0.00 G25 Gooseneck Arm
219253 22247 80 90.000 A 0.000 A 90.00 0.00 0 0.00 Oval LED Tala Lamp - 28OVAL
224673 22693 80 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
231975 23191 80 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
233186 23303 80 28.000 A 0.000 A G10 0.00 0.00 0 0.00 G10 Gooseneck Arm
219254 22247 85 90.000 A 0.000 A 90.00 0.00 0 0.00 Oblo LED Tala Lamp - 28OBLO
224674 22693 85 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
231976 23191 85 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
233187 23303 85 97.000 A 0.000 A G19 0.00 0.00 0 0.00 G19 Gooseneck Arm
219255 22247 90 38.000 A 0.000 A 38.00 0.00 0 0.00 Porcelain III LED Tala Lamp - 28PORC-III
224675 22693 90 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
231977 23191 90 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
233188 23303 90 190.000 A 0.000 A G65 0.00 0.00 0 0.00 G65 Gooseneck Arm
219256 22247 95 90.000 A 0.000 A 90.00 0.00 0 0.00 Noma LED Tala Lamp - 28NOMA
224676 22693 95 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
231978 23191 95 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
233189 23303 95 158.000 A 0.000 A G64 0.00 0.00 0 0.00 G64 Gooseneck Arm
219257 22247 100 90.000 A 0.000 A 90.00 0.00 0 0.00 Enno LED Tala Lamp - 28ENNO
224677 22693 100 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
231979 23191 100 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
233190 23303 100 8.000 A 0.000 A G12 0.00 0.00 0 0.00 G12 Gooseneck Arm
219258 22247 105 93.000 A 0.000 A 93.00 0.00 0 0.00 Voronoi I LED Tala Lamp - 28VORN-I
224678 22693 105 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
231980 23191 105 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
233191 23303 105 10.000 A 0.000 A G13 0.00 0.00 0 0.00 G13 Gooseneck Arm
219259 22247 110 145.000 A 0.000 A 145.00 0.00 0 0.00 Voronoi II LED Tala Lamp - 28VORN-II
224679 22693 110 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
231981 23191 110 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
233192 23303 110 23.000 A 0.000 A G32 0.00 0.00 0 0.00 G32 Gooseneck Arm
224680 22693 115 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
231982 23191 115 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
224681 22693 120 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
231983 23191 120 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
233194 23303 120 35.000 A 0.000 A G2 0.00 0.00 0 0.00 G2 Gooseneck Arm
224689 22693 122 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
224690 22693 124 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
231984 23191 125 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
233195 23303 125 39.000 A 0.000 A G36 0.00 0.00 0 0.00 G36 Gooseneck Arm
224691 22693 126 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
224692 22693 128 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
231985 23191 130 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
233196 23303 130 25.000 A 0.000 A G7 0.00 0.00 0 0.00 G7 Gooseneck Arm
231986 23191 135 29.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
233197 23303 135 32.000 A 0.000 A G9 0.00 0.00 0 0.00 G9 Gooseneck Arm
231987 23191 140 29.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
233198 23303 140 141.000 A 0.000 A G40 0.00 0.00 0 0.00 G40 Gooseneck Arm
231988 23191 145 29.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
224686 22693 150 125.000 A 0.000 A 997 0.00 0.00 0 0.00 997-Raw Brass
231989 23191 150 160.000 A 0.000 A 995 0.00 0.00 0 0.00 995-Raw Copper
224687 22693 155 125.000 A 0.000 A 998 0.00 0.00 0 0.00 998-Weathered Brass
231990 23191 155 200.000 A 0.000 A 996 0.00 0.00 0 0.00 996-Weathered Copper
231991 23191 160 250.000 A 0.000 A 999 0.00 0.00 0 0.00 999-Oil-Rubbed Copper
231992 23191 165 210.000 A 0.000 A 997 0.00 0.00 0 0.00 997-Raw Brass
231993 23191 170 250.000 A 0.000 A 998 0.00 0.00 0 0.00 998-Weathered Brass