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 (
    21942, 23270, 23258, 23234, 23248, 23259, 
    23260, 23261, 23264, 23265, 23266, 
    23267, 23229, 23230, 22396
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00216

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": 182,
          "filtered": 100,
          "index_condition": "a.option_id in (21942,23270,23258,23234,23248,23259,23260,23261,23264,23265,23266,23267,23229,23230,22396) 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
217618 21942 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
221143 22396 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232268 23229 0 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
232300 23230 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232392 23234 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232612 23248 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232807 23258 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232817 23259 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232828 23260 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232839 23261 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232908 23264 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232921 23265 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232934 23266 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232960 23267 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
232975 23270 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
215378 21942 1 0.000 A 0.000 A 0.00 0.00 0 0.00 Standard Bulb E26/GU24
244702 23258 2 79.000 A 0.000 A M103 50.00 0.00 0 0.00 M103-Black
232275 23230 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
232389 23234 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
232606 23248 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
232809 23259 3 0.000 A 0.000 A CLR 0.00 0.00 0 0.00 Clear Glass
232820 23260 3 0.000 A 0.000 A CLR 0.00 0.00 0 0.00 Clear Glass
232831 23261 3 0.000 A 0.000 A CLR 0.00 0.00 0 0.00 Clear Glass
232882 23264 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
232935 23267 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
244703 23258 4 79.000 A 0.000 A M200 50.00 0.00 0 0.00 M200-White
215379 21942 5 0.000 A 0.000 A 0.00 0.00 0 0.00 Integrated LED
221144 22396 5 0.000 A 0.000 A E26 0.00 0.00 0 0.00 Standard Incandescent
232269 23229 5 382.000 A 0.000 A PMDBSP8 0.00 0.00 0 0.00 8' Smooth Pole
232810 23259 5 30.000 A 0.000 A FST 0.00 0.00 0 0.00 Frosted Glass
232821 23260 5 30.000 A 0.000 A FST 0.00 0.00 0 0.00 Frosted Glass
232832 23261 5 20.000 A 0.000 A FST 0.00 0.00 0 0.00 Frosted Glass
232883 23264 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
232936 23267 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
232969 23270 5 0.000 A 0.000 A WHS14 0.00 0.00 0 0.00 14" Shade
244710 23265 5 17.000 A 0.000 A M103 0.00 0.00 0 0.00 M103-Black
244718 23266 5 0.000 A 0.000 A M103 0.00 0.00 0 0.00 M103-Black
244704 23258 6 79.000 A 0.000 A M305 50.00 0.00 0 0.00 M305-Forest Green
244705 23258 8 79.000 A 0.000 A M603 50.00 0.00 0 0.00 M603-Brown
215380 21942 10 0.000 A 0.000 A 0.00 0.00 0 0.00 Nautical LED
221145 22396 10 40.000 A 0.000 A GU24 0.00 0.00 0 0.00 GU24
232270 23229 10 411.000 A 0.000 A PMDBFP8 0.00 0.00 0 0.00 8' Fluted Pole
232277 23230 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
232390 23234 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
232608 23248 10 146.000 A 0.000 A WC 0.00 0.00 0 0.00 Wire Cage
232811 23259 10 30.000 A 0.000 A RIB 0.00 0.00 0 0.00 Ribbed Glass
232822 23260 10 30.000 A 0.000 A RIB 0.00 0.00 0 0.00 Ribbed Glass
232833 23261 10 20.000 A 0.000 A RIB 0.00 0.00 0 0.00 Ribbed Glass
232884 23264 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
232937 23267 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
232970 23270 10 15.000 A 0.000 A WHS16 0.00 0.00 0 0.00 16" Shade
244706 23258 10 79.000 A 0.000 A M415 50.00 0.00 0 0.00 M415-Red
244711 23265 10 17.000 A 0.000 A M200 0.00 0.00 0 0.00 M200-White
244719 23266 10 0.000 A 0.000 A M200 0.00 0.00 0 0.00 M200-White
244707 23258 12 79.000 A 0.000 A M315 50.00 0.00 0 0.00 M315-Seafoam
244708 23258 14 79.000 A 0.000 A M803 50.00 0.00 0 0.00 M803-Dark Grey
232609 23248 15 291.000 A 0.000 A WGG 0.00 0.00 0 0.00 Wire Guard & Glass
232885 23264 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
232938 23267 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
244712 23265 15 17.000 A 0.000 A M305 0.00 0.00 0 0.00 M305-Forest Green
244720 23266 15 0.000 A 0.000 A M305 0.00 0.00 0 0.00 M305-Forest Green
244709 23258 16 79.000 A 0.000 A M210 50.00 0.00 0 0.00 M210-Cream
232279 23230 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
232391 23234 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
232610 23248 20 291.000 A 0.000 A CGG 0.00 0.00 0 0.00 Cast Guard & Glass
232886 23264 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
232939 23267 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
244713 23265 20 17.000 A 0.000 A M603 0.00 0.00 0 0.00 M603-Brown
244721 23266 20 0.000 A 0.000 A M603 0.00 0.00 0 0.00 M603-Brown
232611 23248 25 291.000 A 0.000 A TGG 0.00 0.00 0 0.00 Heavy Duty Guard & Glass
232887 23264 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
232940 23267 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
244714 23265 25 17.000 A 0.000 A M415 0.00 0.00 0 0.00 M415-Red
244722 23266 25 0.000 A 0.000 A M415 0.00 0.00 0 0.00 M415-Red
237016 23264 27 11.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237017 23264 28 11.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
232888 23264 30 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
232941 23267 30 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
244715 23265 30 17.000 A 0.000 A M315 0.00 0.00 0 0.00 M315-Seafoam
244723 23266 30 0.000 A 0.000 A M315 0.00 0.00 0 0.00 M315-Seafoam
245248 23264 32 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
245252 23267 32 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
241295 23264 33 11.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
241300 23267 33 11.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
232889 23264 35 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
232942 23267 35 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
244716 23265 35 17.000 A 0.000 A M803 0.00 0.00 0 0.00 M803-Dark Grey
244724 23266 35 0.000 A 0.000 A M803 0.00 0.00 0 0.00 M803-Dark Grey
232890 23264 40 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
232943 23267 40 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
244717 23265 40 17.000 A 0.000 A M210 0.00 0.00 0 0.00 M210-Cream
244725 23266 40 0.000 A 0.000 A M210 0.00 0.00 0 0.00 M210-Cream
232891 23264 45 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
232944 23267 45 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
232892 23264 50 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
232945 23267 50 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
232777 23258 55 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
232893 23264 55 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
232946 23267 55 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
241296 23264 58 11.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
241301 23267 58 11.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
232778 23258 60 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
232894 23264 60 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
232947 23267 60 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
232779 23258 65 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
232895 23264 65 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
232948 23267 65 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
232780 23258 70 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
232896 23264 70 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
232949 23267 70 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
232781 23258 75 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
232897 23264 75 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
232950 23267 75 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
232782 23258 80 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
232898 23264 80 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
232951 23267 80 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
237008 23258 82 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237009 23258 83 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
232783 23258 85 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
232899 23264 85 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
232952 23267 85 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
245244 23258 86 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
241290 23258 88 18.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
232784 23258 90 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
232900 23264 90 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
232953 23267 90 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
232785 23258 95 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
232901 23264 95 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
232954 23267 95 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241297 23264 98 11.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
241302 23267 98 11.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
232786 23258 100 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
232902 23264 100 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
232955 23267 100 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241303 23267 102 11.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
241298 23264 103 11.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
232787 23258 105 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
232903 23264 105 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
232956 23267 105 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
232788 23258 110 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
232904 23264 110 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
232957 23267 110 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
245253 23267 111 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
245254 23267 112 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
232961 23267 113 11.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
241291 23258 113 18.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
245249 23264 113 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
245250 23264 114 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
232789 23258 115 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
232905 23264 115 11.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
232958 23267 115 11.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
232790 23258 120 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
232906 23264 120 11.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
232959 23267 120 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
245255 23267 122 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241304 23267 123 11.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
232791 23258 125 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
232907 23264 125 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
245251 23264 127 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241299 23264 128 11.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
232792 23258 130 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
232793 23258 135 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
232794 23258 140 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
232795 23258 145 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
232796 23258 150 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241292 23258 153 18.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
232797 23258 155 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241293 23258 158 18.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
232798 23258 160 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
232799 23258 165 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
245245 23258 166 18.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
245246 23258 167 18.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
232808 23258 168 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
232800 23258 170 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
232801 23258 175 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
245247 23258 176 18.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241294 23258 178 18.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
232802 23258 180 160.000 A 0.000 A 995 0.00 0.00 0 0.00 995-Raw Copper
232803 23258 185 200.000 A 0.000 A 996 0.00 0.00 0 0.00 996-Weathered Copper
232804 23258 190 250.000 A 0.000 A 999 0.00 0.00 0 0.00 999-Oil-Rubbed Copper
232805 23258 195 210.000 A 0.000 A 997 0.00 0.00 0 0.00 997-Raw Brass
232806 23258 200 250.000 A 0.000 A 998 0.00 0.00 0 0.00 998-Weathered Brass