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, 21951, 21954, 23324, 22028, 
    22207, 22208, 22209, 23846, 21941
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00283

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": 162,
          "filtered": 100,
          "index_condition": "a.option_id in (23845,23321,21951,21954,23324,22028,22207,22208,22209,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
220144 21951 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
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
233387 23321 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
233486 23324 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
215513 21951 3 0.000 A 0.000 A CN36 0.00 0.00 0 0.00 3 Feet of Chain
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
233457 23324 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
233356 23321 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
233458 23324 5 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
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
233357 23321 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
233459 23324 10 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
240416 23846 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
215516 21951 15 25.000 A 0.000 A CN72 0.00 0.00 0 0.00 6 Feet of Chain
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
233358 23321 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
233460 23324 15 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
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
233359 23321 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
233461 23324 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
233360 23321 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
233462 23324 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
233463 23324 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
238325 23324 32 11.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
238326 23324 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
233464 23324 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
241914 23324 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
233465 23324 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
233466 23324 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
233467 23324 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
233468 23324 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
233469 23324 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
241915 23324 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
233470 23324 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
233471 23324 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
233472 23324 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
233473 23324 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
233474 23324 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
233475 23324 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
233476 23324 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
233477 23324 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
241916 23324 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
233478 23324 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
241917 23324 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
233479 23324 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
233480 23324 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
233481 23324 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
233482 23324 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
233483 23324 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
241918 23324 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
233484 23324 140 11.000 A 0.000 A 930 0.00 0.00 0 0.00 930-Painted Aged Brass
233485 23324 145 11.000 A 0.000 A 940 0.00 0.00 0 0.00 940-Painted Aged Copper
242175 23324 148 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
242176 23324 150 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242177 23324 152 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
242178 23324 154 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite