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 (
    21966, 22013, 22067, 22020, 22021, 22024, 
    22025, 23132, 22644, 22645, 23594
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00207

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": 159,
          "filtered": 100,
          "index_condition": "a.option_id in (21966,22013,22067,22020,22021,22024,22025,23132,22644,22645,23594) 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
220130 21966 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
220143 22013 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
220231 22067 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220265 22020 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220266 22021 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220269 22024 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220270 22025 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
223755 22644 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
223760 22645 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
230951 23132 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
238250 23594 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
244558 22013 2 79.000 A 0.000 A M103 50.00 0.00 0 0.00 M103-Black
215658 21966 3 0.000 A 0.000 A WHS12 0.00 0.00 0 0.00 12" Shade
217019 22020 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
217095 22024 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
217630 22067 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
238248 23594 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
244566 22021 3 17.000 A 0.000 A M103 0.00 0.00 0 0.00 M103-Black
244582 22025 3 17.000 A 0.000 A M103 0.00 0.00 0 0.00 M103-Black
244559 22013 4 79.000 A 0.000 A M200 50.00 0.00 0 0.00 M200-White
215659 21966 5 14.000 A 0.000 A WHS14 0.00 0.00 0 0.00 14" Shade
217020 22020 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
217096 22024 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
217631 22067 5 87.000 A 0.000 A ACR 0.00 0.00 0 {"229346":"75.00","229347":"85.00","229348":"95.00","229349":"140.00","236650":"165.00"} 0.00 Acrylic Diffuser
223756 22644 5 0.000 A 0.000 A 2700K 0.00 0.00 0 0.00 2700K - Warm White/Soft White
223761 22645 5 0.000 A 0.000 A DL 0.00 0.00 0 0.00 Domed Lens
230952 23132 5 0.000 A 0.000 A LED11 0.00 0.00 0 0.00 850 Lumens
238249 23594 5 58.000 A 0.000 A DD 0.00 0.00 0 0.00 Dusk-to-Dawn Photocell
244567 22021 5 17.000 A 0.000 A M200 0.00 0.00 0 0.00 M200-White
244583 22025 5 17.000 A 0.000 A M200 0.00 0.00 0 0.00 M200-White
244560 22013 6 79.000 A 0.000 A M305 50.00 0.00 0 0.00 M305-Forest Green
244561 22013 8 79.000 A 0.000 A M603 50.00 0.00 0 0.00 M603-Brown
217021 22020 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
217097 22024 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
217632 22067 10 49.000 A 0.000 A WC 0.00 0.00 0 0.00 Wire Cage
223757 22644 10 0.000 A 0.000 A 3000K 0.00 0.00 0 0.00 3000K - Neutral White
223762 22645 10 0.000 A 0.000 A FL 0.00 0.00 0 0.00 Flat Lens
230953 23132 10 0.000 A 0.000 A LED16 0.00 0.00 0 0.00 1250 Lumens
238245 21966 10 29.000 A 0.000 A WHS16 0.00 0.00 0 0.00 16" Shade
244562 22013 10 79.000 A 0.000 A M415 50.00 0.00 0 0.00 M415-Red
244568 22021 10 17.000 A 0.000 A M305 0.00 0.00 0 0.00 M305-Forest Green
244584 22025 10 17.000 A 0.000 A M305 0.00 0.00 0 0.00 M305-Forest Green
244563 22013 12 79.000 A 0.000 A M315 50.00 0.00 0 0.00 M315-Seafoam
244564 22013 14 79.000 A 0.000 A M803 50.00 0.00 0 0.00 M803-Dark Grey
217022 22020 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
217098 22024 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
223758 22644 15 0.000 A 0.000 A 3500K 0.00 0.00 0 0.00 3500K - Bright White
230954 23132 15 5.000 A 0.000 A LED27 0.00 0.00 0 1.25 2000 Lumens
244569 22021 15 17.000 A 0.000 A M603 0.00 0.00 0 0.00 M603-Brown
244585 22025 15 17.000 A 0.000 A M603 0.00 0.00 0 0.00 M603-Brown
244565 22013 16 79.000 A 0.000 A M210 50.00 0.00 0 0.00 M210-Cream
217023 22020 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
217099 22024 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
223759 22644 20 0.000 A 0.000 A 4000K 0.00 0.00 0 0.00 4000K - Cool White
244570 22021 20 17.000 A 0.000 A M415 0.00 0.00 0 0.00 M415-Red
244586 22025 20 17.000 A 0.000 A M415 0.00 0.00 0 0.00 M415-Red
217024 22020 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
217100 22024 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
244571 22021 25 17.000 A 0.000 A M315 0.00 0.00 0 0.00 M315-Seafoam
244587 22025 25 17.000 A 0.000 A M315 0.00 0.00 0 0.00 M315-Seafoam
236886 22024 27 11.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
236888 22020 27 11.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
236887 22024 28 11.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
236889 22020 28 11.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
217025 22020 30 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
217101 22024 30 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
244572 22021 30 17.000 A 0.000 A M803 0.00 0.00 0 0.00 M803-Dark Grey
244588 22025 30 17.000 A 0.000 A M803 0.00 0.00 0 0.00 M803-Dark Grey
245126 22020 32 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
241595 22020 33 11.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
241605 22024 33 11.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
217026 22020 35 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
217102 22024 35 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
244573 22021 35 17.000 A 0.000 A M210 0.00 0.00 0 0.00 M210-Cream
244589 22025 35 17.000 A 0.000 A M210 0.00 0.00 0 0.00 M210-Cream
217027 22020 40 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
217103 22024 40 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
217028 22020 45 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
217104 22024 45 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
217029 22020 50 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
217105 22024 50 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
216947 22013 55 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
217030 22020 55 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
217106 22024 55 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
241596 22020 58 11.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
241606 22024 58 11.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
216948 22013 60 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
217031 22020 60 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
217107 22024 60 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
216949 22013 65 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
217032 22020 65 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
217108 22024 65 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
216950 22013 70 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
217033 22020 70 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
217109 22024 70 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
216951 22013 75 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
217034 22020 75 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
217110 22024 75 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
216952 22013 80 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
217035 22020 80 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
217111 22024 80 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
236882 22013 82 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
236883 22013 83 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
216953 22013 85 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
217036 22020 85 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
217112 22024 85 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
245122 22013 86 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
241590 22013 88 18.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
216954 22013 90 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
217037 22020 90 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
217113 22024 90 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
216955 22013 95 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
217038 22020 95 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
217114 22024 95 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241597 22020 98 11.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
241607 22024 98 11.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
216956 22013 100 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
217039 22020 100 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
217115 22024 100 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241598 22020 103 11.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
241608 22024 103 11.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
216957 22013 105 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
217040 22020 105 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
217116 22024 105 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
216958 22013 110 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
217041 22020 110 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
217117 22024 110 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
245127 22020 112 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
225921 22024 113 11.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
241591 22013 113 18.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
245128 22020 114 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
216959 22013 115 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
217042 22020 115 11.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
217118 22024 115 11.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
216960 22013 120 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
217043 22020 120 11.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
217119 22024 120 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
241609 22024 123 11.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
216961 22013 125 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
217044 22020 125 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
245129 22020 127 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241599 22020 128 11.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
216962 22013 130 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
216963 22013 135 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
216964 22013 140 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
216965 22013 145 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
216966 22013 150 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241592 22013 153 18.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
216967 22013 155 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241593 22013 158 18.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
216968 22013 160 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
216969 22013 165 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
245123 22013 166 18.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
245124 22013 167 18.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
225920 22013 168 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
216970 22013 170 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
216971 22013 175 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
245125 22013 176 18.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241594 22013 178 18.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey