Sandbox

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 (
    22565, 21970, 22570, 22571, 23059, 22567, 
    22568, 22566, 22573, 22574, 22572, 
    22246, 22569, 22575, 22576, 23710, 
    23711
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00249

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": 255,
          "filtered": 100,
          "index_condition": "a.option_id in (22565,21970,22570,22571,23059,22567,22568,22566,22573,22574,22572,22246,22569,22575,22576,23710,23711) 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_type	nav_skus	commercial_modifier	variant_name
218476	21970	0	0.000	A	A	unselected	0.00	Please Select
220319	22246	0	0.000	A	A	unselected	0.00	Please Select
222695	22566	0	0.000	A	A	skip	0.00	Please Select
222698	22567	0	0.000	A	A	skip	0.00	Please Select
222699	22568	0	0.000	A	A	unselected	0.00	Please Select
222704	22565	0	0.000	A	A	skip	0.00	Please Select
222708	22569	0	0.000	A	A	skip	0.00	Please Select
222709	22570	0	0.000	A	A	skip	0.00	Please Skip
222726	22571	0	0.000	A	A	skip	0.00	Please Select
222727	22572	0	0.000	A	A	skip	0.00	Please Select
222760	22573	0	0.000	A	A	unselected	0.00	Select Option
222761	22574	0	0.000	A	A	skip	0.00	Please Select
222805	22575	0	0.000	A	A	unselected	0.00	Please Select
222806	22576	0	0.000	A	A	skip	0.00	Please Select
230424	23059	0	0.000	A	A	skip	0.00	Please Select
239207	23710	0	0.000	A	A	unselected	0.00	Please Select
239235	23711	0	0.000	A	A	skip	0.00	Please Select
215713	21970	3	0.000	A	A	100	0.00	100-Black
219225	22246	3	0.000	A	A		0.00	None
222691	22565	3	0.000	A	A	WHD12	0.00	12" Shade
222693	22566	3	0.000	A	A	SBA12	0.00	12" Shade
222696	22567	3	0.000	A	A	WHS12	0.00	12" Shade
222705	22569	3	0.000	A	A	YDM12	0.00	12" Shade
222712	22571	3	0.000	A	A	SBK	0.00	Standard Black
222730	22573	3	0.000	A	A	100	0.00	100-Black
222764	22575	3	59.000	A	A	150	0.00	150-Black
239208	23711	3	0.000	A	A	200	0.00	200-White
215714	21970	5	0.000	A	A	105	0.00	105-Textured Black
222692	22565	5	13.000	A	A	WHD14	0.00	14" Shade
222694	22566	5	13.000	A	A	SBA14	0.00	14" Shade
222697	22567	5	14.000	A	A	WHS14	0.00	14" Shade
222700	22568	5	0.000	A	A	AUS11	0.00	11" Shade
222706	22569	5	13.000	A	A	YDM14	0.00	14" Shade
222710	22570	5	0.000	A	A	ST12	0.00	12" Stem
222713	22571	5	5.000	A	A	SWH	0.00	Standard White
222728	22572	5	0.000	A	A	100	0.00	100-Black
222731	22573	5	0.000	A	A	105	0.00	105-Textured Black
222762	22574	5	0.000	A	A	975	0.00	975-Galvanized
222765	22575	5	59.000	A	A	250	0.00	250-White
222807	22576	5	0.000	A	A	975	0.00	975-Galvanized
230425	23059	5	0.000	A	A	100	0.00	100-Black
215715	21970	10	0.000	A	A	200	0.00	200-White
219227	22246	10	13.000	A	A		13.00	LED Clear ST19 - 31-4.5FSST19DIM/821
222701	22568	10	10.000	A	A	AUS13	0.00	13" Shade
222711	22570	10	13.000	A	A	ST18	0.00	18" Stem
222714	22571	10	10.000	A	A	CSB	0.00	Black Cloth
222729	22572	10	0.000	A	A	200	0.00	200-White
222732	22573	10	0.000	A	A	200	0.00	200-White
222763	22574	10	36.000	A	A	997	0.00	997-Raw Brass
222766	22575	10	59.000	A	A	350	0.00	350-Vintage Green
222808	22576	10	0.000	A	A	100	0.00	100-Black
230426	23059	10	0.000	A	A	200	0.00	200-White
215716	21970	15	0.000	A	A	300	0.00	300-Dark Green
222715	22571	15	10.000	A	A	SLVR	0.00	Silver Cloth
222733	22573	15	0.000	A	A	300	0.00	300-Dark Green
222767	22575	15	59.000	A	A	355	0.00	355-Jadite
222809	22576	15	13.000	A	A	997	0.00	997-Raw Brass
242997	22246	15	13.000	A	A		13.00	LED Amber ST19 - 31-4.5FSST19DIM/820/A
215738	21970	18	0.000	A	A	975	0.00	975-Galvanized
222755	22573	18	0.000	A	A	975	0.00	975-Galvanized
215731	21970	19	0.000	A	A	605	0.00	605-Textured Rust
222748	22573	19	0.000	A	A	605	0.00	605-Textured Rust
219229	22246	20	14.000	A	A		20.00	LED Smoky ST19 - 31-4.5FSST19DIM/840/S
222716	22571	20	10.000	A	A	CRM	0.00	Creme Cloth
222770	22575	20	59.000	A	A	650	0.00	650-Bronze
222810	22576	20	17.000	A	A	998	0.00	998-Weathered Brass
237036	21970	21	18.000	A	A	106	0.00	106-Matte Black
237240	22573	21	18.000	A	A	106	0.00	106-Matte Black
237037	21970	22	18.000	A	A	206	0.00	206-Matte White
237241	22573	22	18.000	A	A	206	0.00	206-Matte White
215717	21970	24	18.000	A	A	307	0.00	307-Emerald Green
222734	22573	24	18.000	A	A	307	0.00	307-Emerald Green
222717	22571	25	10.000	A	A	BPNK	0.00	Blush Pink Cloth
222768	22575	25	76.000	A	A	455	0.00	455-Cherry Red
242135	21970	25	18.000	A	A	311	0.00	311-Jadite
242916	22573	25	18.000	A	A	311	0.00	311-Jadite
241141	21970	26	18.000	A	A	320	0.00	320-Fern Green
241700	22573	26	18.000	A	A	320	0.00	320-Fern Green
215718	21970	28	18.000	A	A	370	0.00	370-Mint
222735	22573	28	18.000	A	A	370	0.00	370-Mint
215719	21970	30	18.000	A	A	380	0.00	380-Chartreuse
222718	22571	30	20.000	A	A	CNF	0.00	Confetti Cloth
222736	22573	30	18.000	A	A	380	0.00	380-Chartreuse
222769	22575	30	76.000	A	A	550	0.00	550-Yellow
215720	21970	35	18.000	A	A	390	0.00	390-Teal
222719	22571	35	10.000	A	A	RST	0.00	Rust Cloth
222737	22573	35	18.000	A	A	390	0.00	390-Teal
222771	22575	35	76.000	A	A	750	0.00	750-Cobalt Blue
215721	21970	40	18.000	A	A	400	0.00	400-Barn Red
222720	22571	40	15.000	A	A	RBS	0.00	Barn Red & Black Swirl Cloth
222738	22573	40	18.000	A	A	400	0.00	400-Barn Red
222772	22575	40	76.000	A	A	765	0.00	765-Delphite Blue
215722	21970	45	18.000	A	A	420	0.00	420-Orange
222721	22571	45	10.000	A	A	ORG	0.00	Orange Cloth
222739	22573	45	18.000	A	A	420	0.00	420-Orange
222773	22575	45	76.000	A	A	850	0.00	850-Graphite
241142	21970	48	18.000	A	A	430	0.00	430-Soft Clay
241701	22573	48	18.000	A	A	430	0.00	430-Soft Clay
215723	21970	50	18.000	A	A	470	0.00	470-Watermelon
222722	22571	50	10.000	A	A	YLW	0.00	Buttery Yellow Cloth
222740	22573	50	18.000	A	A	470	0.00	470-Watermelon
222774	22575	50	76.000	A	A	950	0.00	950-Metallic Chrome
215724	21970	55	18.000	A	A	480	0.00	480-Blush Pink
222723	22571	55	10.000	A	A	NGRN	0.00	Neon Green Cloth
222741	22573	55	18.000	A	A	480	0.00	480-Blush Pink
222775	22575	55	0.000	A	A	100	0.00	100-Black
239178	23710	55	0.000	A	A	100	0.00	100-Black
239209	23711	55	18.000	A	A	100	0.00	100-Black
215725	21970	60	18.000	A	A	490	0.00	490-Magenta
222724	22571	60	10.000	A	A	EGRN	0.00	Emerald Green Cloth
222742	22573	60	18.000	A	A	490	0.00	490-Magenta
222776	22575	60	0.000	A	A	105	0.00	105-Textured Black
239179	23710	60	0.000	A	A	105	0.00	105-Textured Black
239210	23711	60	18.000	A	A	105	0.00	105-Textured Black
215726	21970	65	18.000	A	A	495	0.00	495-Sherbet Orange
222725	22571	65	20.000	A	A	FGRN	0.00	Fern Green Chevron Cloth
222743	22573	65	18.000	A	A	495	0.00	495-Sherbet Orange
222777	22575	65	0.000	A	A	200	0.00	200-White
239180	23710	65	0.000	A	A	200	0.00	200-White
215727	21970	70	18.000	A	A	500	0.00	500-Buttery Yellow
222744	22573	70	18.000	A	A	500	0.00	500-Buttery Yellow
222778	22575	70	0.000	A	A	300	0.00	300-Dark Green
239181	23710	70	0.000	A	A	300	0.00	300-Dark Green
239211	23711	70	18.000	A	A	300	0.00	300-Dark Green
243091	22571	70	10.000	A	A	SFM	0.00	Seafoam Cloth
215728	21970	75	18.000	A	A	570	0.00	570-Sunflower
222745	22573	75	18.000	A	A	570	0.00	570-Sunflower
222779	22575	75	0.000	A	A	975	0.00	975-Galvanized
239182	23710	75	0.000	A	A	975	0.00	975-Galvanized
239212	23711	75	18.000	A	A	975	0.00	975-Galvanized
243092	22571	75	10.000	A	A	SBLU	0.00	Sky Blue Cloth
215729	21970	80	18.000	A	A	600	0.00	600-Bronze
222746	22573	80	18.000	A	A	600	0.00	600-Bronze
222780	22575	80	0.000	A	A	605	0.00	605-Textured Rust
239183	23710	80	0.000	A	A	605	0.00	605-Textured Rust
239213	23711	80	18.000	A	A	605	0.00	605-Textured Rust
243093	22571	80	10.000	A	A	RBLU	0.00	Royal Blue Cloth
239205	23710	82	18.000	A	A	106	0.00	106-Matte Black
239238	23711	82	18.000	A	A	106	0.00	106-Matte Black
239239	23711	83	18.000	A	A	206	0.00	206-Matte White
239206	23710	84	18.000	A	A	206	0.00	206-Matte White
215730	21970	85	18.000	A	A	601	0.00	601-Chocolate
222747	22573	85	18.000	A	A	601	0.00	601-Chocolate
222781	22575	85	18.000	A	A	307	0.00	307-Emerald Green
239184	23710	85	18.000	A	A	307	0.00	307-Emerald Green
239214	23711	85	18.000	A	A	307	0.00	307-Emerald Green
243094	22571	85	10.000	A	A	NVY	0.00	Navy Cloth
241746	22575	86	18.000	A	A	320	0.00	320-Fern Green
242908	23710	86	18.000	A	A	311	0.00	311-Jadite
242912	23711	86	18.000	A	A	311	0.00	311-Jadite
222804	22575	88	18.000	A	A	370	0.00	370-Mint
241702	22573	88	18.000	A	A	607	0.00	607-Taupe
241736	23710	88	18.000	A	A	320	0.00	320-Fern Green
241741	23711	88	18.000	A	A	320	0.00	320-Fern Green
222782	22575	90	18.000	A	A	380	0.00	380-Chartreuse
239185	23710	90	18.000	A	A	370	0.00	370-Mint
239215	23711	90	18.000	A	A	370	0.00	370-Mint
241143	21970	90	18.000	A	A	607	0.00	607-Taupe
215732	21970	95	18.000	A	A	615	0.00	615-Oil-Rubbed Bronze
222749	22573	95	18.000	A	A	615	0.00	615-Oil-Rubbed Bronze
222783	22575	95	18.000	A	A	390	0.00	390-Teal
239186	23710	95	18.000	A	A	380	0.00	380-Chartreuse
239216	23711	95	18.000	A	A	380	0.00	380-Chartreuse
241144	21970	98	18.000	A	A	630	0.00	630-Beige
241703	22573	98	18.000	A	A	630	0.00	630-Beige
215733	21970	100	18.000	A	A	700	0.00	700-Royal Blue
222750	22573	100	18.000	A	A	700	0.00	700-Royal Blue
222784	22575	100	18.000	A	A	400	0.00	400-Barn Red
239187	23710	100	18.000	A	A	390	0.00	390-Teal
239217	23711	100	18.000	A	A	390	0.00	390-Teal
215734	21970	105	18.000	A	A	705	0.00	705-Navy
222751	22573	105	18.000	A	A	705	0.00	705-Navy
222785	22575	105	18.000	A	A	420	0.00	420-Orange
239188	23710	105	18.000	A	A	400	0.00	400-Barn Red
239218	23711	105	18.000	A	A	400	0.00	400-Barn Red
242917	22573	107	18.000	A	A	710	0.00	710-Cobalt Blue
241747	22575	108	18.000	A	A	430	0.00	430-Soft Clay
242918	22573	109	18.000	A	A	715	0.00	715-Delphite Blue
215735	21970	110	18.000	A	A	790	0.00	790-Lavender
222752	22573	110	18.000	A	A	790	0.00	790-Lavender
222786	22575	110	18.000	A	A	470	0.00	470-Watermelon
239189	23710	110	18.000	A	A	420	0.00	420-Orange
239219	23711	110	18.000	A	A	420	0.00	420-Orange
242136	21970	112	18.000	A	A	710	0.00	710-Cobalt Blue
241737	23710	113	18.000	A	A	430	0.00	430-Soft Clay
241742	23711	113	18.000	A	A	430	0.00	430-Soft Clay
242137	21970	113	18.000	A	A	715	0.00	715-Delphite Blue
215736	21970	115	18.000	A	A	800	0.00	800-Industrial Grey
222753	22573	115	18.000	A	A	800	0.00	800-Industrial Grey
222787	22575	115	18.000	A	A	480	0.00	480-Blush Pink
239190	23710	115	18.000	A	A	470	0.00	470-Watermelon
239220	23711	115	18.000	A	A	470	0.00	470-Watermelon
215737	21970	120	18.000	A	A	805	0.00	805-Charcoal Granite
222754	22573	120	18.000	A	A	805	0.00	805-Charcoal Granite
222788	22575	120	18.000	A	A	490	0.00	490-Magenta
239191	23710	120	18.000	A	A	480	0.00	480-Blush Pink
239221	23711	120	18.000	A	A	480	0.00	480-Blush Pink
242919	22573	122	18.000	A	A	810	0.00	810-Graphite
241704	22573	123	18.000	A	A	815	0.00	815-Light Grey
242138	21970	123	18.000	A	A	810	0.00	810-Graphite
222789	22575	125	18.000	A	A	495	0.00	495-Sherbet Orange
239192	23710	125	18.000	A	A	490	0.00	490-Magenta
239222	23711	125	18.000	A	A	490	0.00	490-Magenta
241145	21970	125	18.000	A	A	815	0.00	815-Light Grey
222790	22575	130	18.000	A	A	500	0.00	500-Buttery Yellow
239193	23710	130	18.000	A	A	495	0.00	495-Sherbet Orange
239223	23711	130	18.000	A	A	495	0.00	495-Sherbet Orange
222791	22575	135	18.000	A	A	570	0.00	570-Sunflower
239194	23710	135	18.000	A	A	500	0.00	500-Buttery Yellow
239224	23711	135	18.000	A	A	500	0.00	500-Buttery Yellow
222792	22575	140	18.000	A	A	600	0.00	600-Bronze
239195	23710	140	18.000	A	A	570	0.00	570-Sunflower
239225	23711	140	18.000	A	A	570	0.00	570-Sunflower
222793	22575	145	18.000	A	A	601	0.00	601-Chocolate
239196	23710	145	18.000	A	A	600	0.00	600-Bronze
239226	23711	145	18.000	A	A	600	0.00	600-Bronze
241748	22575	148	18.000	A	A	607	0.00	607-Taupe
222794	22575	150	18.000	A	A	615	0.00	615-Oil-Rubbed Bronze
239197	23710	150	18.000	A	A	601	0.00	601-Chocolate
239227	23711	150	18.000	A	A	601	0.00	601-Chocolate
241743	23711	152	18.000	A	A	607	0.00	607-Taupe
241738	23710	153	18.000	A	A	607	0.00	607-Taupe
241749	22575	153	18.000	A	A	630	0.00	630-Beige
222795	22575	155	18.000	A	A	700	0.00	700-Royal Blue
239198	23710	155	18.000	A	A	615	0.00	615-Oil-Rubbed Bronze
239228	23711	155	18.000	A	A	615	0.00	615-Oil-Rubbed Bronze
241739	23710	158	18.000	A	A	630	0.00	630-Beige
241744	23711	158	18.000	A	A	630	0.00	630-Beige
222796	22575	160	18.000	A	A	705	0.00	705-Navy
239199	23710	160	18.000	A	A	700	0.00	700-Royal Blue
239229	23711	160	18.000	A	A	700	0.00	700-Royal Blue
222797	22575	165	18.000	A	A	800	0.00	800-Industrial Grey
239200	23710	165	18.000	A	A	705	0.00	705-Navy
239230	23711	165	18.000	A	A	705	0.00	705-Navy
242913	23711	166	18.000	A	A	710	0.00	710-Cobalt Blue
242909	23710	167	18.000	A	A	710	0.00	710-Cobalt Blue
242914	23711	168	18.000	A	A	715	0.00	715-Delphite Blue
242910	23710	169	18.000	A	A	715	0.00	715-Delphite Blue
222798	22575	170	18.000	A	A	805	0.00	805-Charcoal Granite
239201	23710	170	18.000	A	A	800	0.00	800-Industrial Grey
239231	23711	170	18.000	A	A	800	0.00	800-Industrial Grey
241750	22575	173	18.000	A	A	815	0.00	815-Light Grey
222799	22575	175	61.000	A	A	995	0.00	995-Raw Copper
239202	23710	175	18.000	A	A	805	0.00	805-Charcoal Granite
239232	23711	175	18.000	A	A	805	0.00	805-Charcoal Granite
242911	23710	176	18.000	A	A	810	0.00	810-Graphite
242915	23711	176	18.000	A	A	810	0.00	810-Graphite
241740	23710	178	18.000	A	A	815	0.00	815-Light Grey
241745	23711	178	18.000	A	A	815	0.00	815-Light Grey
222800	22575	180	91.000	A	A	996	0.00	996-Weathered Copper
222801	22575	185	91.000	A	A	999	0.00	999-Oil-Rubbed Copper
222802	22575	190	92.000	A	A	997	0.00	997-Raw Brass
239233	23711	193	161.000	A	A	995	0.00	995-Raw Copper
222803	22575	195	121.000	A	A	998	0.00	998-Weathered Brass
239234	23711	195	225.000	A	A	997	0.00	997-Raw Brass