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 (
    22715, 23857, 23858, 23859, 23860, 22717, 
    22719, 21941, 23669
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00280

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": 171,
          "filtered": 100,
          "index_condition": "a.option_id in (22715,23857,23858,23859,23860,22717,22719,21941,23669) 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
220318 21941 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
225078 22715 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
225140 22717 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
225182 22719 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
239017 23669 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
225139 22717 2 0.000 A 0.000 A 0.00 0.00 22715 0.00 Same as Mig
215376 21941 3 0.000 A 0.000 A E26 0.00 0.00 0 0.00 E26
225037 22715 3 44.000 A 0.000 A 150 0.00 0.00 0 0.00 150-Black
225127 22717 3 6.000 A 0.000 A 150 0.00 0.00 0 0.00 150-Black
225155 22719 3 0.000 A 0.000 A 0.00 0.00 22715 0.00 Same as Mig
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
215377 21941 5 14.000 A 0.000 A GU24 0.00 0.00 0 3.50 GU24
225038 22715 5 44.000 A 0.000 A 250 0.00 0.00 0 0.00 250-White
225128 22717 5 6.000 A 0.000 A 250 0.00 0.00 0 0.00 250-White
225156 22719 5 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
239018 23669 5 0.000 A 0.000 A F7116 0.00 0.00 0 0.00 6-Light | Carbide Black
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
225039 22715 10 44.000 A 0.000 A 350 0.00 0.00 0 0.00 350-Vintage Green
225129 22717 10 6.000 A 0.000 A 350 0.00 0.00 0 0.00 350-Vintage Green
225157 22719 10 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
239019 23669 10 764.000 A 0.000 A F6876 0.00 0.00 0 0.00 6-Light | Modern Bronze
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
225040 22715 15 44.000 A 0.000 A 355 0.00 0.00 0 0.00 355-Jadite
225130 22717 15 6.000 A 0.000 A 355 0.00 0.00 0 0.00 355-Jadite
225158 22719 15 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
239020 23669 15 226.000 A 0.000 A F7118 226.00 0.00 0 0.00 8-Light | Carbide Black
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
225041 22715 20 61.000 A 0.000 A 455 0.00 0.00 0 0.00 455-Cherry Red
225131 22717 20 11.000 A 0.000 A 455 0.00 0.00 0 0.00 455-Cherry Red
225159 22719 20 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
239025 23669 20 1030.000 A 0.000 A F6878 1030.00 0.00 0 0.00 8-Light | Modern Bronze
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
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
225042 22715 25 61.000 A 0.000 A 550 0.00 0.00 0 0.00 550-Yellow
225132 22717 25 11.000 A 0.000 A 550 0.00 0.00 0 0.00 550-Yellow
225160 22719 25 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
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
225043 22715 30 44.000 A 0.000 A 650 0.00 0.00 0 0.00 650-Bronze
225133 22717 30 6.000 A 0.000 A 650 0.00 0.00 0 0.00 650-Bronze
225161 22719 30 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
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
237208 22719 32 6.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237209 22719 33 6.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
225044 22715 35 61.000 A 0.000 A 750 0.00 0.00 0 0.00 750-Cobalt Blue
225134 22717 35 11.000 A 0.000 A 750 0.00 0.00 0 0.00 750-Cobalt Blue
225162 22719 35 6.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
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
242589 22719 38 6.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
225045 22715 40 61.000 A 0.000 A 765 0.00 0.00 0 0.00 765-Delphite Blue
225135 22717 40 11.000 A 0.000 A 765 0.00 0.00 0 0.00 765-Delphite Blue
225163 22719 40 6.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
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
225046 22715 45 61.000 A 0.000 A 850 0.00 0.00 0 0.00 850-Graphite
225136 22717 45 11.000 A 0.000 A 850 0.00 0.00 0 0.00 850-Graphite
225164 22719 45 6.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
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
225047 22715 50 61.000 A 0.000 A 950 0.00 0.00 0 0.00 950-Metallic Chrome
225137 22717 50 11.000 A 0.000 A 950 0.00 0.00 0 0.00 950-Metallic Chrome
225165 22719 50 6.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
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
225048 22715 55 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
225138 22717 55 11.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
225166 22719 55 6.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
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
225049 22715 60 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
225167 22719 60 6.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
242590 22719 63 6.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
225050 22715 65 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
225168 22719 65 6.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
225051 22715 70 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
225169 22719 70 6.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
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
225052 22715 75 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
225170 22719 75 6.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
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
225053 22715 80 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
225171 22719 80 6.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
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
237206 22715 82 29.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237207 22715 83 29.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
225054 22715 85 29.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
225172 22719 85 6.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
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
242584 22715 88 29.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
225055 22715 90 29.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
225173 22719 90 6.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
225056 22715 95 29.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
225174 22719 95 6.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
225057 22715 100 29.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
225175 22719 100 6.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
225058 22715 105 29.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
242591 22719 105 6.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
225059 22715 110 29.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
225176 22719 110 6.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
242585 22715 113 29.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
242592 22719 113 6.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
225060 22715 115 29.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
225177 22719 115 6.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
225061 22715 120 29.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
225178 22719 120 6.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
225062 22715 125 29.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
225179 22719 125 6.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
225063 22715 130 29.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
225180 22719 130 6.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
225064 22715 135 29.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
225181 22719 135 6.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
242593 22719 138 6.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
225065 22715 140 29.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
225066 22715 145 29.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
225067 22715 150 29.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
242586 22715 153 29.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
225068 22715 155 29.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
242587 22715 158 29.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
225069 22715 160 29.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
225070 22715 165 29.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
225071 22715 170 29.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
225072 22715 175 29.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
242588 22715 178 29.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
225073 22715 180 29.000 A 0.000 A 375 0.00 0.00 0 0.00 375-Dark Green
225074 22715 185 29.000 A 0.000 A 376 0.00 0.00 0 0.00 376-Jadite
225075 22715 190 29.000 A 0.000 A 475 0.00 0.00 0 0.00 475-Red
225076 22715 195 29.000 A 0.000 A 476 0.00 0.00 0 0.00 476-Burnt Orange
225077 22715 200 29.000 A 0.000 A 575 0.00 0.00 0 0.00 575-Yellow
225079 22715 205 29.000 A 0.000 A 675 0.00 0.00 0 0.00 675-Brown
225080 22715 210 29.000 A 0.000 A 775 0.00 0.00 0 0.00 775-Blue
238343 22715 213 29.000 A 0.000 A 776 0.00 0.00 0 0.00 776-Delphite Blue
225081 22715 215 29.000 A 0.000 A 875 0.00 0.00 0 0.00 875-Grey