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 (
    22952, 22310, 21973, 23070, 23768, 23769, 
    22653, 22571, 22572
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00278

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": 161,
          "filtered": 100,
          "index_condition": "a.option_id in (22952,22310,21973,23070,23768,23769,22653,22571,22572) 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
220155 22310 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
220194 21973 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
222726 22571 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
222727 22572 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
223836 22653 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
228624 22952 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
230497 23070 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
239822 23768 0 0.000 A 0.000 A unselected 0.00 0.00 0 0.00 Please Select
239829 23769 0 0.000 A 0.000 A skip 0.00 0.00 0 0.00 Please Select
215783 21973 3 0.000 A 0.000 A 0.00 0.00 22952 0.00 Same as Shade
219969 22310 3 0.000 A 0.000 A 6 0.00 0.00 0 0.00 6" Stem
222712 22571 3 0.000 A 0.000 A SBK 0.00 0.00 0 0.00 Standard Black
223824 22653 3 0.000 A 0.000 A 0.00 0.00 0 0.00 None
228594 22952 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
230489 23070 3 0.000 A 0.000 A 0.00 0.00 0 0.00 None
239787 23768 3 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
239825 23769 3 0.000 A 0.000 A NA 0.00 0.00 0 0.00 No Extended Arm
215784 21973 5 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
219970 22310 5 3.000 A 0.000 A 12 0.00 0.00 0 0.00 12" Stem
222713 22571 5 5.000 A 0.000 A SWH 0.00 0.00 0 0.00 Standard White
222728 22572 5 0.000 A 0.000 A 100 0.00 0.00 0 0.00 100-Black
223825 22653 5 19.000 A 0.000 A 19.00 0.00 0 0.00 LED Silver Bowl G25 - 31-4.5FG25DIM/827/SB/R
228595 22952 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
230490 23070 5 57.000 A 0.000 A 57.00 0.00 0 0.00 LED Silver Bowl G25 - 31-4.5FG25DIM/827/SB/R
239788 23768 5 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
239826 23769 5 40.000 A 0.000 A EXT 0.00 0.00 0 0.00 Extended Arm
215785 21973 10 0.000 A 0.000 A 105 0.00 0.00 0 0.00 105-Textured Black
219971 22310 10 5.000 A 0.000 A 18 0.00 0.00 0 0.00 18" Stem
222714 22571 10 10.000 A 0.000 A CSB 0.00 0.00 0 0.00 Black Cloth
222729 22572 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
223826 22653 10 13.000 A 0.000 A 13.00 0.00 0 0.00 LED Clear ST19 - 31-4.5FSST19DIM/821
228596 22952 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
230491 23070 10 39.000 A 0.000 A 39.00 0.00 0 0.00 LED Clear ST19 - 31-4.5FSST19DIM/821
239789 23768 10 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
215786 21973 15 0.000 A 0.000 A 200 0.00 0.00 0 0.00 200-White
219972 22310 15 7.000 A 0.000 A 24 0.00 0.00 0 0.00 24" Stem
222715 22571 15 10.000 A 0.000 A SLVR 0.00 0.00 0 0.00 Silver Cloth
228597 22952 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
239790 23768 15 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
242977 23070 15 39.000 A 0.000 A 39.00 0.00 0 0.00 LED Amber ST19 - 31-4.5FSST19DIM/820/A
242985 22653 15 13.000 A 0.000 A 0.00 0.00 0 0.00 LED Amber ST19 - 31-4.5FSST19DIM/820/A
228619 22952 18 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
228612 22952 19 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
215787 21973 20 0.000 A 0.000 A 300 0.00 0.00 0 0.00 300-Dark Green
222716 22571 20 10.000 A 0.000 A CRM 0.00 0.00 0 0.00 Creme Cloth
223828 22653 20 14.000 A 0.000 A 14.00 0.00 0 0.00 LED Smoky ST19 - 31-4.5FSST19DIM/840/S
224600 22310 20 12.000 A 0.000 A 36 0.00 0.00 0 0.00 36" Stem
230493 23070 20 42.000 A 0.000 A 42.00 0.00 0 0.00 LED Smoky ST19 - 31-4.5FSST19DIM/840/S
239791 23768 20 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
237074 22952 21 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237075 22952 22 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
228598 22952 24 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
215788 21973 25 0.000 A 0.000 A 975 0.00 0.00 0 0.00 975-Galvanized
222717 22571 25 10.000 A 0.000 A BPNK 0.00 0.00 0 0.00 Blush Pink Cloth
239792 23768 25 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
242509 22952 25 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
241555 22952 26 18.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
239823 23768 27 18.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
228599 22952 28 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
239824 23768 28 18.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
239913 21973 28 0.000 A 0.000 A 980 0.00 0.00 0 0.00 980-Brushed Aluminum
222718 22571 30 20.000 A 0.000 A CNF 0.00 0.00 0 0.00 Confetti Cloth
228600 22952 30 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
239793 23768 30 18.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
242712 23768 33 18.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
215790 21973 35 0.000 A 0.000 A 605 0.00 0.00 0 0.00 605-Textured Rust
222719 22571 35 10.000 A 0.000 A RST 0.00 0.00 0 0.00 Rust Cloth
228601 22952 35 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
239794 23768 35 18.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
237162 21973 37 11.000 A 0.000 A 106 0.00 0.00 0 0.00 106-Matte Black
237163 21973 38 11.000 A 0.000 A 206 0.00 0.00 0 0.00 206-Matte White
227180 21973 39 11.000 A 0.000 A 307 0.00 0.00 0 0.00 307-Emerald Green
222720 22571 40 15.000 A 0.000 A RBS 0.00 0.00 0 0.00 Barn Red & Black Swirl Cloth
228602 22952 40 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
239795 23768 40 18.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
241560 21973 41 11.000 A 0.000 A 320 0.00 0.00 0 0.00 320-Fern Green
215791 21973 43 11.000 A 0.000 A 370 0.00 0.00 0 0.00 370-Mint
215792 21973 45 11.000 A 0.000 A 380 0.00 0.00 0 0.00 380-Chartreuse
222721 22571 45 10.000 A 0.000 A ORG 0.00 0.00 0 0.00 Orange Cloth
228603 22952 45 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
239796 23768 45 18.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
241556 22952 48 18.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
215793 21973 50 11.000 A 0.000 A 390 0.00 0.00 0 0.00 390-Teal
222722 22571 50 10.000 A 0.000 A YLW 0.00 0.00 0 0.00 Buttery Yellow Cloth
228604 22952 50 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
239797 23768 50 18.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
215794 21973 55 11.000 A 0.000 A 400 0.00 0.00 0 0.00 400-Barn Red
222723 22571 55 10.000 A 0.000 A NGRN 0.00 0.00 0 0.00 Neon Green Cloth
228605 22952 55 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
239798 23768 55 18.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
242713 23768 58 18.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
215795 21973 60 11.000 A 0.000 A 420 0.00 0.00 0 0.00 420-Orange
222724 22571 60 10.000 A 0.000 A EGRN 0.00 0.00 0 0.00 Emerald Green Cloth
228606 22952 60 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
239799 23768 60 18.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
241561 21973 63 11.000 A 0.000 A 430 0.00 0.00 0 0.00 430-Soft Clay
215796 21973 65 11.000 A 0.000 A 470 0.00 0.00 0 0.00 470-Watermelon
222725 22571 65 20.000 A 0.000 A FGRN 0.00 0.00 0 0.00 Fern Green Chevron Cloth
228607 22952 65 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
239800 23768 65 18.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
215797 21973 70 11.000 A 0.000 A 480 0.00 0.00 0 0.00 480-Blush Pink
228608 22952 70 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
239801 23768 70 18.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
243091 22571 70 10.000 A 0.000 A SFM 0.00 0.00 0 0.00 Seafoam Cloth
215798 21973 75 11.000 A 0.000 A 490 0.00 0.00 0 0.00 490-Magenta
228609 22952 75 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
239802 23768 75 18.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
243092 22571 75 10.000 A 0.000 A SBLU 0.00 0.00 0 0.00 Sky Blue Cloth
215799 21973 80 11.000 A 0.000 A 495 0.00 0.00 0 0.00 495-Sherbet Orange
228610 22952 80 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
239803 23768 80 18.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
243093 22571 80 10.000 A 0.000 A RBLU 0.00 0.00 0 0.00 Royal Blue Cloth
215800 21973 85 11.000 A 0.000 A 500 0.00 0.00 0 0.00 500-Buttery Yellow
228611 22952 85 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
239804 23768 85 18.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
243094 22571 85 10.000 A 0.000 A NVY 0.00 0.00 0 0.00 Navy Cloth
241557 22952 88 18.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
215801 21973 90 11.000 A 0.000 A 570 0.00 0.00 0 0.00 570-Sunflower
239805 23768 90 18.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
215802 21973 95 11.000 A 0.000 A 600 0.00 0.00 0 0.00 600-Bronze
228613 22952 95 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
239806 23768 95 18.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
241558 22952 98 18.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
242714 23768 98 18.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
215803 21973 100 11.000 A 0.000 A 601 0.00 0.00 0 0.00 601-Chocolate
228614 22952 100 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
239807 23768 100 18.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
241562 21973 103 11.000 A 0.000 A 607 0.00 0.00 0 0.00 607-Taupe
242715 23768 103 18.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
215804 21973 105 11.000 A 0.000 A 615 0.00 0.00 0 0.00 615-Oil-Rubbed Bronze
228615 22952 105 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
239808 23768 105 18.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
242510 22952 107 18.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
241563 21973 108 11.000 A 0.000 A 630 0.00 0.00 0 0.00 630-Beige
242511 22952 109 18.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
215805 21973 110 11.000 A 0.000 A 700 0.00 0.00 0 0.00 700-Royal Blue
228616 22952 110 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
239809 23768 110 18.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
215806 21973 115 11.000 A 0.000 A 705 0.00 0.00 0 0.00 705-Navy
228617 22952 115 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
239810 23768 115 18.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
215807 21973 120 11.000 A 0.000 A 790 0.00 0.00 0 0.00 790-Lavender
228618 22952 120 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
239811 23768 120 18.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
242512 22952 122 18.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
241559 22952 123 18.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
215808 21973 125 11.000 A 0.000 A 800 0.00 0.00 0 0.00 800-Industrial Grey
239812 23768 125 18.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
242716 23768 128 18.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
215809 21973 130 11.000 A 0.000 A 805 0.00 0.00 0 0.00 805-Charcoal Granite
239813 23768 130 18.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
241564 21973 133 11.000 A 0.000 A 815 0.00 0.00 0 0.00 815-Light Grey
239814 23768 135 29.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242513 21973 135 11.000 A 0.000 A 311 0.00 0.00 0 0.00 311-Jadite
242514 21973 137 11.000 A 0.000 A 710 0.00 0.00 0 0.00 710-Cobalt Blue
242515 21973 139 11.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
239815 23768 140 29.000 A 0.000 A 715 0.00 0.00 0 0.00 715-Delphite Blue
242516 21973 141 11.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
239816 23768 145 29.000 A 0.000 A 810 0.00 0.00 0 0.00 810-Graphite
239820 23768 165 100.000 A 0.000 A 997 0.00 0.00 0 0.00 997-Raw Brass
239821 23768 170 121.000 A 0.000 A 998 0.00 0.00 0 0.00 998-Weathered Brass