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 (
    23775, 23726, 23596, 23595, 23857, 23858, 
    23859, 23860, 23333, 23590
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00215

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 (23775,23726,23596,23595,23857,23858,23859,23860,23333,23590) 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
233635 23333 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
238222 23590 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
238258 23595 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
238294 23596 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
239341 23726 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
239949 23775 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
240507 23857 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
240536 23858 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
240559 23859 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
240582 23860 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
238228 23590 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 None
238256 23595 3 0.000 A 0.000 A ASH 0.00 0.00 0 0.00 Ash Wood
238259 23596 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
239338 23726 3 0.000 A 0.000 A ATO9 0.00 0.00 0 0.00 9" Cylinder
240506 23857 3 0.000 A 0.000 A 7FT 0.00 0.00 0 0.00 7' of Cord
240514 23858 3 0.000 A 0.000 A SBK 0.00 0.00 0 0.00 Standard Black
240537 23859 3 5.000 A 0.000 A SBK 0.00 0.00 0 2.10 Standard Black
240560 23860 3 30.000 A 0.000 A SBK 0.00 0.00 0 5.26 Standard Black
238223 23590 5 99.000 A 0.000 A ASH 0.00 0.00 0 0.00 Ash Wood
238260 23596 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
239339 23726 5 13.000 A 0.000 A ATO15 0.00 0.00 0 0.00 15" Cylinder
239950 23775 5 0.000 A 0.000 A 0.00 0.00 0 0.00 Standard Incandescent
240508 23857 5 0.000 A 0.000 A 12FT 0.00 0.00 0 0.00 12' of Cord
240515 23858 5 5.000 A 0.000 A SWH 0.00 0.00 0 0.32 Standard White
240538 23859 5 10.000 A 0.000 A SWH 0.00 0.00 0 3.18 Standard White
240561 23860 5 35.000 A 0.000 A SWH 0.00 0.00 0 7.26 Standard White
238224 23590 7 99.000 A 0.000 A WAL 0.00 0.00 0 0.00 Walnut Wood
233606 23333 8 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
233607 23333 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
238261 23596 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
239340 23726 10 24.000 A 0.000 A ATO21 0.00 0.00 0 0.00 21" Cylinder
239951 23775 10 0.000 A 0.000 A 0.00 0.00 0 0.00 Integrated LED
240509 23857 10 0.000 A 0.000 A 20FT 0.00 0.00 0 0.00 20' of Cord
240516 23858 10 10.000 A 0.000 A CSB 0.00 0.00 0 3.66 Black Cloth
240539 23859 10 15.000 A 0.000 A CSB 0.00 0.00 0 5.70 Black Cloth
240562 23860 10 40.000 A 0.000 A CSB 0.00 0.00 0 11.46 Black Cloth
233608 23333 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
238257 23595 15 0.000 A 0.000 A WAL 0.00 0.00 0 0.00 Walnut Wood
238262 23596 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
239342 23726 15 35.000 A 0.000 A ATO27 0.00 0.00 0 0.00 27" Cylinder
240517 23858 15 10.000 A 0.000 A SLVR 0.00 0.00 0 3.66 Silver Cloth
240540 23859 15 20.000 A 0.000 A SLVR 0.00 0.00 0 7.02 Silver Cloth
240563 23860 15 40.000 A 0.000 A SLVR 0.00 0.00 0 13.66 Silver Cloth
233609 23333 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
238263 23596 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
240518 23858 20 10.000 A 0.000 A CRM 0.00 0.00 0 3.66 Creme Cloth
240541 23859 20 20.000 A 0.000 A CRM 0.00 0.00 0 7.02 Creme Cloth
240564 23860 20 40.000 A 0.000 A CRM 0.00 0.00 0 13.66 Creme Cloth
240526 23858 22 10.000 A 0.000 A BPNK 0.00 0.00 0 3.66 Blush Pink Cloth
240549 23859 22 25.000 A 0.000 A BPNK 0.00 0.00 0 8.98 Blush Pink Cloth
240572 23860 22 50.000 A 0.000 A BPNK 0.00 0.00 0 17.00 Blush Pink Cloth
238295 23596 23 0.000 A 0.000 A 980 0.00 0.00 0 0.00 980-Brushed Aluminum
240535 23858 23 20.000 A 0.000 A CNF 0.00 0.00 0 10.06 Confetti Cloth
240558 23859 23 25.000 A 0.000 A CNF 0.00 0.00 0 8.43 Confetti Cloth
240581 23860 23 50.000 A 0.000 A CNF 0.00 0.00 0 16.00 Confetti Cloth
238264 23596 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
240519 23858 25 10.000 A 0.000 A RST 0.00 0.00 0 3.66 Rust Cloth
240542 23859 25 25.000 A 0.000 A RST 0.00 0.00 0 7.02 Rust Cloth
240565 23860 25 50.000 A 0.000 A RST 0.00 0.00 0 17.00 Rust Cloth
238297 23596 27 7.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
238298 23596 28 7.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
233610 23333 30 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
238265 23596 30 7.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
240520 23858 30 15.000 A 0.000 A RBS 0.00 0.00 0 6.81 Barn Red & Black Swirl Cloth
240543 23859 30 20.000 A 0.000 A RBS 0.00 0.00 0 13.00 Barn Red & Black Swirl Cloth
240566 23860 30 40.000 A 0.000 A RBS 0.00 0.00 0 13.66 Barn Red & Black Swirl Cloth
237270 23333 33 6.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
241413 23596 33 7.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
237271 23333 34 6.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
233611 23333 35 0.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
238266 23596 35 7.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
240521 23858 35 10.000 A 0.000 A ORG 0.00 0.00 0 3.66 Orange Cloth
240544 23859 35 20.000 A 0.000 A ORG 0.00 0.00 0 6.78 Orange Cloth
240567 23860 35 40.000 A 0.000 A ORG 0.00 0.00 0 13.26 Orange Cloth
241418 23333 38 6.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
233612 23333 40 6.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
238267 23596 40 7.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
240522 23858 40 10.000 A 0.000 A YLW 0.00 0.00 0 3.66 Buttery Yellow Cloth
240545 23859 40 42.000 A 0.000 A YLW 0.00 0.00 0 14.94 Buttery Yellow Cloth
240568 23860 40 80.000 A 0.000 A YLW 0.00 0.00 0 26.86 Buttery Yellow Cloth
233613 23333 45 6.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
238268 23596 45 7.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
240523 23858 45 10.000 A 0.000 A NGRN 0.00 0.00 0 3.66 Neon Green Cloth
240546 23859 45 25.000 A 0.000 A NGRN 0.00 0.00 0 8.93 Neon Green Cloth
240569 23860 45 50.000 A 0.000 A NGRN 0.00 0.00 0 16.84 Neon Green Cloth
233614 23333 50 6.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
238269 23596 50 7.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
240524 23858 50 10.000 A 0.000 A EGRN 0.00 0.00 0 3.66 Emerald Green Cloth
240547 23859 50 25.000 A 0.000 A EGRN 0.00 0.00 0 9.06 Emerald Green Cloth
240570 23860 50 50.000 A 0.000 A EGRN 0.00 0.00 0 17.06 Emerald Green Cloth
233615 23333 55 6.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
238270 23596 55 7.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
240525 23858 55 20.000 A 0.000 A FGRN 0.00 0.00 0 10.06 Fern Green Chevron Cloth
240548 23859 55 25.000 A 0.000 A FGRN 0.00 0.00 0 8.94 Fern Green Chevron Cloth
240571 23860 55 50.000 A 0.000 A FGRN 0.00 0.00 0 16.86 Fern Green Chevron Cloth
241414 23596 58 7.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
233616 23333 60 6.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
238271 23596 60 7.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
241419 23333 63 6.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
233617 23333 65 6.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
238272 23596 65 7.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
233618 23333 70 6.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
238273 23596 70 7.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
240527 23858 70 10.000 A 0.000 A SFM 0.00 0.00 0 3.66 Seafoam Cloth
240550 23859 70 25.000 A 0.000 A SFM 0.00 0.00 0 9.18 Seafoam Cloth
240573 23860 70 50.000 A 0.000 A SFM 0.00 0.00 0 17.26 Seafoam Cloth
233619 23333 75 6.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
238274 23596 75 7.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
240528 23858 75 10.000 A 0.000 A SBLU 0.00 0.00 0 3.66 Sky Blue Cloth
240551 23859 75 25.000 A 0.000 A SBLU 0.00 0.00 0 8.94 Sky Blue Cloth
240574 23860 75 50.000 A 0.000 A SBLU 0.00 0.00 0 16.86 Sky Blue Cloth
233620 23333 80 6.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
238275 23596 80 7.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
240529 23858 80 10.000 A 0.000 A RBLU 0.00 0.00 0 3.66 Royal Blue Cloth
240552 23859 80 20.000 A 0.000 A RBLU 0.00 0.00 0 6.81 Royal Blue Cloth
240575 23860 80 40.000 A 0.000 A RBLU 0.00 0.00 0 13.31 Royal Blue Cloth
233621 23333 85 6.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
238276 23596 85 7.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
240530 23858 85 10.000 A 0.000 A NVY 0.00 0.00 0 3.66 Navy Cloth
240553 23859 85 42.000 A 0.000 A NVY 0.00 0.00 0 14.94 Navy Cloth
240576 23860 85 80.000 A 0.000 A NVY 0.00 0.00 0 26.86 Navy Cloth
233622 23333 90 6.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
238277 23596 90 7.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
233623 23333 95 6.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
238278 23596 95 7.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241415 23596 98 7.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
233624 23333 100 6.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
238279 23596 100 7.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241416 23596 103 7.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
241420 23333 103 6.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
233625 23333 105 6.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
238280 23596 105 7.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
241421 23333 108 6.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
233626 23333 110 6.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
238281 23596 110 7.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
233627 23333 115 6.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
238282 23596 115 7.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
233628 23333 120 6.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
238283 23596 120 7.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
233629 23333 125 6.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
238284 23596 125 7.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
241417 23596 127 7.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
238296 23596 128 7.000 A 0.000 A 990 0.00 0.00 0 0.00 990-Raw/Unfinished
233630 23333 130 6.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
241422 23333 132 6.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
236690 23333 133 6.000 A 0.000 A 990 0.00 0.00 0 0.00 990-Raw/Unfinished
242227 23333 135 6.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
242228 23333 137 6.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242229 23333 139 6.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
242230 23333 141 6.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
238289 23596 150 36.000 A 0.000 A 995 0.00 0.00 0 0.00 995-Raw Copper
238290 23596 155 41.000 A 0.000 A 996 0.00 0.00 0 0.00 996-Weathered Copper
238291 23596 160 41.000 A 0.000 A 999 0.00 0.00 0 0.00 999-Oil-Rubbed Copper
238292 23596 165 47.000 A 0.000 A 997 0.00 0.00 0 0.00 997-Raw Brass
238293 23596 170 53.000 A 0.000 A 998 0.00 0.00 0 0.00 998-Weathered Brass
242223 23596 172 7.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
242224 23596 174 7.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242225 23596 176 7.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
242226 23596 178 7.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite