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 (
    23845, 23321, 21954, 23084, 23322, 22028, 
    22207, 22208, 22209, 23328, 23846, 
    21941
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00225

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": 169,
          "filtered": 100,
          "index_condition": "a.option_id in (23845,23321,21954,23084,23322,22028,22207,22208,22209,23328,23846,21941) 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
220173 21954 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220229 22028 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220258 22207 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220259 22208 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220260 22209 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220318 21941 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
230564 23084 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
233387 23321 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
233417 23322 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
233546 23328 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
240413 23845 0 0.000 A 0.000 A LAR30 0.00 0.00 0 0.00 30" Shade
240444 23846 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
215556 21954 3 0.000 A 0.000 A SBK 0.00 0.00 0 0.00 Standard Black
217138 22028 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
218968 22207 3 0.000 A 0.000 A CLR 0.00 0.00 0 0.00 Clear Glass
218978 22208 3 0.000 A 0.000 A CLR 0.00 0.00 0 0.00 Clear Glass
218988 22209 3 0.000 A 0.000 A CLR 0.00 0.00 0 0.00 Clear Glass
233355 23321 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
233388 23322 3 0.000 A 0.000 A 0.00 0.00 23321 0.00 Same as Shade
240414 23846 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
215377 21941 5 14.000 A 0.000 A GU24 0.00 0.00 0 3.50 GU24
215557 21954 5 5.000 A 0.000 A SWH 0.00 0.00 0 0.32 Standard White
217139 22028 5 97.000 A 0.000 A WGG 0.00 0.00 0 0.00 Wire Guard & Glass
218969 22207 5 10.000 A 0.000 A FST 0.00 0.00 0 0.00 Frosted Glass
218979 22208 5 10.000 A 0.000 A FST 0.00 0.00 0 0.00 Frosted Glass
218989 22209 5 10.000 A 0.000 A FST 0.00 0.00 0 0.00 Frosted Glass
230565 23084 5 0.000 A 0.000 A 18 0.00 0.00 0 0.00 18" Cable & Cord
233356 23321 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
233389 23322 5 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
233547 23328 5 0.000 A 0.000 A CLR 0.00 0.00 0 2.85 Clear Glass
240415 23846 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
215558 21954 10 10.000 A 0.000 A CSB 0.00 0.00 0 3.66 Black Cloth
217140 22028 10 97.000 A 0.000 A CGG 0.00 0.00 0 0.00 Cast Guard & Glass
218970 22207 10 10.000 A 0.000 A RIB 0.00 0.00 0 0.00 Ribbed Glass
218980 22208 10 10.000 A 0.000 A RIB 0.00 0.00 0 0.00 Ribbed Glass
218990 22209 10 10.000 A 0.000 A RIB 0.00 0.00 0 0.00 Ribbed Glass
230566 23084 10 7.000 A 0.000 A 24 0.00 0.00 0 0.00 24" Cable & Cord
233357 23321 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
233390 23322 10 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
233548 23328 10 10.000 A 0.000 A FST 0.00 0.00 0 1.88 Frosted Glass
240416 23846 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
215559 21954 15 10.000 A 0.000 A SLVR 0.00 0.00 0 3.66 Silver Cloth
217141 22028 15 97.000 A 0.000 A TGG 0.00 0.00 0 0.00 Heavy Duty Guard & Glass
230567 23084 15 13.000 A 0.000 A 30 0.00 0.00 0 0.00 30" Cable & Cord
233358 23321 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
233391 23322 15 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
233549 23328 15 10.000 A 0.000 A RIB 0.00 0.00 0 3.15 Ribbed Glass
240417 23846 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
215560 21954 20 10.000 A 0.000 A CRM 0.00 0.00 0 3.66 Creme Cloth
230568 23084 20 18.000 A 0.000 A 36 0.00 0.00 0 0.00 36" Cable & Cord
233359 23321 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
233392 23322 20 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
240418 23846 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
215561 21954 25 10.000 A 0.000 A BPNK 0.00 0.00 0 3.66 Blush Pink Cloth
230569 23084 25 24.000 A 0.000 A 42 0.00 0.00 0 0.00 42" Cable & Cord
233360 23321 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
233393 23322 25 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
240419 23846 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
238319 23321 27 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
238320 23321 28 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
215562 21954 30 30.000 A 0.000 A CNF 0.00 0.00 0 10.66 Confetti Cloth
233361 23321 30 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
233394 23322 30 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
240420 23846 30 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
238321 23322 32 11.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
238322 23322 33 11.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
241909 23321 33 18.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
241919 23846 33 11.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
215563 21954 35 10.000 A 0.000 A RST 0.00 0.00 0 3.66 Rust Cloth
233362 23321 35 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
233395 23322 35 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
240421 23846 35 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
241924 23322 38 11.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
215564 21954 40 15.000 A 0.000 A RBS 0.00 0.00 0 6.81 Barn Red & Black Swirl Cloth
233363 23321 40 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
233396 23322 40 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
240422 23846 40 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
215565 21954 45 10.000 A 0.000 A ORG 0.00 0.00 0 3.66 Orange Cloth
233364 23321 45 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
233397 23322 45 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
240423 23846 45 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
215566 21954 50 10.000 A 0.000 A YLW 0.00 0.00 0 3.66 Buttery Yellow Cloth
233365 23321 50 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
233398 23322 50 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
240424 23846 50 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
215567 21954 55 10.000 A 0.000 A NGRN 0.00 0.00 0 3.66 Neon Green Cloth
233366 23321 55 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
233399 23322 55 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
240425 23846 55 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
241920 23846 57 11.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
241910 23321 58 18.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
215568 21954 60 10.000 A 0.000 A EGRN 0.00 0.00 0 3.66 Emerald Green Cloth
233367 23321 60 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
233400 23322 60 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
240426 23846 60 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
241925 23322 63 11.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
215569 21954 65 30.000 A 0.000 A FGRN 0.00 0.00 0 10.66 Fern Green Chevron Cloth
233368 23321 65 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
233401 23322 65 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
240427 23846 65 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
233369 23321 70 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
233402 23322 70 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
240428 23846 70 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
243103 21954 70 10.000 A 0.000 A SFM 0.00 0.00 0 3.66 Seafoam Cloth
233370 23321 75 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
233403 23322 75 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
240429 23846 75 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
243104 21954 75 10.000 A 0.000 A SBLU 0.00 0.00 0 3.66 Sky Blue Cloth
233371 23321 80 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
233404 23322 80 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
240430 23846 80 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
243105 21954 80 10.000 A 0.000 A RBLU 0.00 0.00 0 3.66 Royal Blue Cloth
233372 23321 85 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
233405 23322 85 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
240431 23846 85 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
243106 21954 85 10.000 A 0.000 A NVY 0.00 0.00 0 3.66 Navy Cloth
233373 23321 90 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
233406 23322 90 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
240432 23846 90 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
233374 23321 95 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
233407 23322 95 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
240433 23846 95 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241911 23321 98 18.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
241921 23846 98 11.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
233375 23321 100 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
233408 23322 100 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
240434 23846 100 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241912 23321 103 18.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
241922 23846 103 11.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
233376 23321 105 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
240435 23846 105 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
241926 23322 105 11.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
233377 23321 110 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
233409 23322 110 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
240436 23846 110 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
241927 23322 113 11.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
233378 23321 115 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
233410 23322 115 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
240437 23846 115 11.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
233379 23321 120 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
233411 23322 120 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
240438 23846 120 11.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
233380 23321 125 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
233412 23322 125 11.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
240439 23846 125 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
241913 23321 126 18.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
241923 23846 126 11.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
233385 23321 127 18.000 A 0.000 A 930 0.00 0.00 0 0.00 930-Painted Aged Brass
240445 23846 127 17.000 A 0.000 A 930 0.00 0.00 0 0.00 930-Painted Aged Brass
240446 23846 128 17.000 A 0.000 A 940 0.00 0.00 0 0.00 940-Painted Aged Copper
233386 23321 129 18.000 A 0.000 A 940 0.00 0.00 0 0.00 940-Painted Aged Copper
233413 23322 130 11.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
242179 23846 130 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
242171 23321 132 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
242180 23846 132 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242172 23321 134 18.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242181 23846 134 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
233414 23322 135 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
242173 23321 136 18.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
242182 23846 136 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241928 23322 138 11.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
242174 23321 138 18.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
233415 23322 140 11.000 A 0.000 A 930 0.00 0.00 0 0.00 930-Painted Aged Brass
233416 23322 145 11.000 A 0.000 A 940 0.00 0.00 0 0.00 940-Painted Aged Copper
242183 23322 148 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
242184 23322 150 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242185 23322 152 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
242186 23322 154 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite