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 (
    22934, 24027, 24012, 24013, 21943, 23900, 
    24016, 22631, 21941
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00190

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": 189,
          "filtered": 100,
          "index_condition": "a.option_id in (22934,24027,24012,24013,21943,23900,24016,22631,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
220170 21943 0 0.000 A 0.000 A unselected 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
223650 22631 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
228384 22934 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
243113 23900 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
244311 24012 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
244354 24013 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
244404 24016 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
244454 24027 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
244330 24013 2 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
244405 24016 2 145.000 A 0.000 A ASH 0.00 0.00 0 0.00 Ash Wood
215376 21941 3 0.000 A 0.000 A E26 0.00 0.00 0 0.00 E26
215381 21943 3 0.000 A 0.000 A SBK 0.00 0.00 0 0.00 Standard Black
244285 24012 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
244328 24013 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
244406 24016 4 145.000 A 0.000 A MPL 0.00 0.00 0 0.00 Maple Wood
215377 21941 5 14.000 A 0.000 A GU24 0.00 0.00 0 3.50 GU24
215382 21943 5 5.000 A 0.000 A SWH 0.00 0.00 0 0.32 Standard White
223620 22631 5 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
228385 22934 5 0.000 A 0.000 A 0.00 0.00 0 0.00 Standard Bulb E26/GU24
244286 24012 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
244329 24013 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
244455 24027 5 0.000 A 0.000 A ISO12 0.00 0.00 0 0.00 12" Shade
244407 24016 7 145.000 A 0.000 A WAL 0.00 0.00 0 0.00 Walnut Wood
244379 24016 8 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
244408 24016 9 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
215383 21943 10 10.000 A 0.000 A CSB 0.00 0.00 0 3.66 Black Cloth
223621 22631 10 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
228386 22934 10 0.000 A 0.000 A 0.00 0.00 0 0.00 Integrated LED
244287 24012 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
244380 24016 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
244456 24027 10 99.000 A 0.000 A ISO24 0.00 0.00 0 0.00 24" Shade
215384 21943 15 10.000 A 0.000 A SLVR 0.00 0.00 0 3.66 Silver Cloth
223622 22631 15 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
244288 24012 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
244331 24013 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
244381 24016 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
244457 24027 15 165.000 A 0.000 A ISO32 0.00 0.00 0 0.00 32" Shade
244310 24012 18 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
244353 24013 18 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
244303 24012 19 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
244346 24013 19 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
215385 21943 20 10.000 A 0.000 A CRM 0.00 0.00 0 3.66 Creme Cloth
223623 22631 20 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
243117 23900 20 18.000 A 0.000 A 36 0.00 0.00 0 0.00 36" Cable & Cord
244382 24016 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
244312 24012 21 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
244355 24013 21 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
215394 21943 22 10.000 A 0.000 A BPNK 0.00 0.00 0 3.66 Blush Pink Cloth
244313 24012 22 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
244356 24013 22 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
215403 21943 23 30.000 A 0.000 A CNF 0.00 0.00 0 10.06 Confetti Cloth
244289 24012 24 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
244332 24013 24 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
215386 21943 25 10.000 A 0.000 A RST 0.00 0.00 0 3.66 Rust Cloth
223624 22631 25 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
243118 23900 25 24.000 A 0.000 A 72 0.00 0.00 0 0.00 72" Cable & Cord
244314 24012 26 18.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
244357 24013 26 18.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
244290 24012 28 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
244333 24013 28 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
244420 22631 28 0.000 A 0.000 A 980 0.00 0.00 0 0.00 980-Brushed Aluminum
215387 21943 30 15.000 A 0.000 A RBS 0.00 0.00 0 6.81 Barn Red & Black Swirl Cloth
223625 22631 30 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
243119 23900 30 42.000 A 0.000 A 108 0.00 0.00 0 0.00 108" Cable & Cord
244291 24012 30 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
244334 24013 30 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
244383 24016 30 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
237192 22631 32 6.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
244409 24016 32 0.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237193 22631 33 6.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
244410 24016 33 0.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
215388 21943 35 10.000 A 0.000 A ORG 0.00 0.00 0 3.66 Orange Cloth Cord
223626 22631 35 6.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
244292 24012 35 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
244335 24013 35 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
244384 24016 35 0.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
242239 22631 36 6.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
241433 22631 38 6.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
244411 24016 38 0.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
215389 21943 40 10.000 A 0.000 A YLW 0.00 0.00 0 3.66 Buttery Cloth Cord
223627 22631 40 6.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
244293 24012 40 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
244336 24013 40 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
244385 24016 40 0.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
215390 21943 45 10.000 A 0.000 A NGRN 0.00 0.00 0 3.66 Neon Green Cloth
223628 22631 45 6.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
244294 24012 45 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
244337 24013 45 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
244386 24016 45 0.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
244315 24012 47 18.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
244358 24013 47 18.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
215391 21943 50 10.000 A 0.000 A EGRN 0.00 0.00 0 3.66 Emerald Green Cloth
223629 22631 50 6.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
244295 24012 50 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
244338 24013 50 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
244387 24016 50 0.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
215392 21943 55 30.000 A 0.000 A FGRN 0.00 0.00 0 10.06 Fern Green Chevron Cloth
223630 22631 55 6.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
244296 24012 55 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
244339 24013 55 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
244388 24016 55 0.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
223631 22631 60 6.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
244297 24012 60 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
244340 24013 60 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
244389 24016 60 0.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
241434 22631 63 6.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
244412 24016 63 0.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
223632 22631 65 6.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
244298 24012 65 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
244341 24013 65 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
244390 24016 65 0.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
215395 21943 70 10.000 A 0.000 A SFM 0.00 0.00 0 3.66 Seafoam Cloth
223633 22631 70 6.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
244299 24012 70 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
244342 24013 70 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
244391 24016 70 0.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
215396 21943 75 10.000 A 0.000 A SBLU 0.00 0.00 0 3.66 Sky Blue Cloth
223634 22631 75 6.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
244300 24012 75 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
244343 24013 75 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
244392 24016 75 0.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
215397 21943 80 10.000 A 0.000 A RBLU 0.00 0.00 0 3.66 Royal Blue Cloth
223635 22631 80 6.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
244301 24012 80 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
244344 24013 80 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
244393 24016 80 0.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
215398 21943 85 10.000 A 0.000 A NVY 0.00 0.00 0 3.66 Navy Cloth
223636 22631 85 6.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
244302 24012 85 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
244345 24013 85 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
244394 24016 85 0.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
223637 22631 90 6.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
244316 24012 90 18.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
244359 24013 90 18.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
244395 24016 90 0.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
244317 24012 92 18.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
244360 24013 92 18.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
223638 22631 95 6.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
244304 24012 95 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
244347 24013 95 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
244396 24016 95 0.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
223639 22631 100 6.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
244305 24012 100 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
244348 24013 100 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
244397 24016 100 0.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241435 22631 103 6.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
244413 24016 104 0.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
223640 22631 105 6.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
244306 24012 105 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
244349 24013 105 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
244398 24016 105 0.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241436 22631 108 6.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
223641 22631 110 6.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
244307 24012 110 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
244350 24013 110 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
244399 24016 110 0.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
244414 24016 113 0.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
223642 22631 115 6.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
244308 24012 115 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
244351 24013 115 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
244400 24016 115 0.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
242240 22631 116 6.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242241 22631 118 6.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
223643 22631 120 6.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
244309 24012 120 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
244352 24013 120 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
244401 24016 120 0.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
223644 22631 125 6.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
244318 24012 125 18.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
244361 24013 125 18.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
244402 24016 125 0.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
223645 22631 130 6.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
244319 24012 130 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
244362 24013 130 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
244403 24016 130 0.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
242242 22631 132 6.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241437 22631 133 6.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
244415 24016 133 0.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
244320 24012 135 18.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
244363 24013 135 18.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
244416 24016 136 0.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
244417 24016 138 0.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
244321 24012 140 18.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
244364 24013 140 18.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
244418 24016 140 0.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
244419 24016 142 0.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
244322 24012 145 18.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
244365 24013 145 18.000 A 0.000 A 815 0.00 0.00 0 0.00 810-Graphite