Sandbox

SELECT 
  b.variant_name, 
  a.*, 
  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 b.variant_id = a.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 
  1 
  AND a.option_id IN (
    35778, 35777, 35776, 35775, 35774, 35773, 
    35772, 35771, 35770, 35769, 35751, 
    35708, 35652, 35651, 35650, 35644, 
    36091, 36090, 36089, 36088, 36087, 
    36086, 36085, 36084, 36083, 36082, 
    36081, 36080, 36079, 36078, 36077, 
    36076, 36075, 36074, 36072, 36071, 
    36070, 36069, 36068, 36067, 36066, 
    36065, 36064, 36063, 36062, 36061, 
    36060, 36059, 36058, 35879, 35612, 
    35611, 35757, 35756, 35755, 35700, 
    35600, 35599, 35595, 35594, 35593, 
    35592, 35591, 35629, 35623, 35886, 
    35544, 35543, 35537, 35536, 35557, 
    35556, 35449, 35434, 74818, 35541, 
    35540, 35406, 35889, 35398
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00299

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	range	status,option_id,option_id_2	option_id	6		160	Using index condition; Using filesort
1	SIMPLE	b	eq_ref	PRIMARY	PRIMARY	9	toolboxdigitalshop_dev.a.variant_id,const	1	Using where
1	SIMPLE	shared_option_variants	eq_ref	PRIMARY,company_id	PRIMARY	7	toolboxdigitalshop_dev.a.variant_id,const	1

Result

variant_name	variant_id	option_id	position	modifier	modifier_type	weight_modifier_type	point_modifier_type	status
MP3	70862	35398	1	0.000	A	A	A	A
MP3	70878	35406	1	0.000	A	A	A	A
MP3	70934	35434	1	0.000	A	A	A	A
MP3	70964	35449	1	0.000	A	A	A	A
MP3	71138	35536	1	0.000	A	A	A	A
MP3	71140	35537	1	0.000	A	A	A	A
MP3	71146	35540	1	0.000	A	A	A	A
MP3	71148	35541	1	0.000	A	A	A	A
MP3	71152	35543	1	0.000	A	A	A	A
MP3	71154	35544	1	0.000	A	A	A	A
MP3	71178	35556	1	0.000	A	A	A	A
MP3	71180	35557	1	0.000	A	A	A	A
MP3	71248	35591	1	0.000	A	A	A	A
MP3	71250	35592	1	0.000	A	A	A	A
MP3	71252	35593	1	0.000	A	A	A	A
MP3	71254	35594	1	0.000	A	A	A	A
MP3	71256	35595	1	0.000	A	A	A	A
MP3	71264	35599	1	0.000	A	A	A	A
MP3	71266	35600	1	0.000	A	A	A	A
MP3	71288	35611	1	0.000	A	A	A	A
MP3	71290	35612	1	0.000	A	A	A	A
MP3	71312	35623	1	0.000	A	A	A	A
MP3	71324	35629	1	0.000	A	A	A	A
MP3	71354	35644	1	0.000	A	A	A	A
MP3	71366	35650	1	0.000	A	A	A	A
MP3	71368	35651	1	0.000	A	A	A	A
MP3	71370	35652	1	0.000	A	A	A	A
MP3	71466	35700	1	0.000	A	A	A	A
MP3	71482	35708	1	0.000	A	A	A	A
MP3	71568	35751	1	0.000	A	A	A	A
MP3	71576	35755	1	0.000	A	A	A	A
MP3	71578	35756	1	0.000	A	A	A	A
MP3	71580	35757	1	0.000	A	A	A	A
MP3	71604	35769	1	0.000	A	A	A	A
MP3	71606	35770	1	0.000	A	A	A	A
MP3	71608	35771	1	0.000	A	A	A	A
MP3	71610	35772	1	0.000	A	A	A	A
MP3	71612	35773	1	0.000	A	A	A	A
MP3	71614	35774	1	0.000	A	A	A	A
MP3	71616	35775	1	0.000	A	A	A	A
MP3	71618	35776	1	0.000	A	A	A	A
MP3	71620	35777	1	0.000	A	A	A	A
MP3	71622	35778	1	0.000	A	A	A	A
MP3	71824	35879	1	0.000	A	A	A	A
MP3	71838	35886	1	0.000	A	A	A	A
MP3	71844	35889	1	0.000	A	A	A	A
MP3	72182	36058	1	0.000	A	A	A	A
MP3	72184	36059	1	0.000	A	A	A	A
MP3	72186	36060	1	0.000	A	A	A	A
MP3	72188	36061	1	0.000	A	A	A	A
MP3	72190	36062	1	0.000	A	A	A	A
MP3	72192	36063	1	0.000	A	A	A	A
MP3	72194	36064	1	0.000	A	A	A	A
MP3	72196	36065	1	0.000	A	A	A	A
MP3	72198	36066	1	0.000	A	A	A	A
MP3	72200	36067	1	0.000	A	A	A	A
MP3	72202	36068	1	0.000	A	A	A	A
MP3	72204	36069	1	0.000	A	A	A	A
MP3	72206	36070	1	0.000	A	A	A	A
MP3	72208	36071	1	0.000	A	A	A	A
MP3	72210	36072	1	0.000	A	A	A	A
MP3	72214	36074	1	0.000	A	A	A	A
MP3	72216	36075	1	0.000	A	A	A	A
MP3	72218	36076	1	0.000	A	A	A	A
MP3	72220	36077	1	0.000	A	A	A	A
MP3	72222	36078	1	0.000	A	A	A	A
MP3	72224	36079	1	0.000	A	A	A	A
MP3	72226	36080	1	0.000	A	A	A	A
MP3	72228	36081	1	0.000	A	A	A	A
MP3	72230	36082	1	0.000	A	A	A	A
MP3	72232	36083	1	0.000	A	A	A	A
MP3	72234	36084	1	0.000	A	A	A	A
MP3	72236	36085	1	0.000	A	A	A	A
MP3	72238	36086	1	0.000	A	A	A	A
MP3	72240	36087	1	0.000	A	A	A	A
MP3	72242	36088	1	0.000	A	A	A	A
MP3	72244	36089	1	0.000	A	A	A	A
MP3	72246	36090	1	0.000	A	A	A	A
MP3	72248	36091	1	0.000	A	A	A	A
MP3	150172	74818	1	0.000	A	A	A	A
WAV	70863	35398	2	0.300	A	A	A	A
WAV	70879	35406	2	0.300	A	A	A	A
WAV	70935	35434	2	0.300	A	A	A	A
WAV	70965	35449	2	0.300	A	A	A	A
WAV	71139	35536	2	0.300	A	A	A	A
WAV	71141	35537	2	0.300	A	A	A	A
WAV	71147	35540	2	0.300	A	A	A	A
WAV	71149	35541	2	0.300	A	A	A	A
WAV	71153	35543	2	0.300	A	A	A	A
WAV	71155	35544	2	0.300	A	A	A	A
WAV	71179	35556	2	0.300	A	A	A	A
WAV	71181	35557	2	0.300	A	A	A	A
WAV	71249	35591	2	0.300	A	A	A	A
WAV	71251	35592	2	0.300	A	A	A	A
WAV	71253	35593	2	0.300	A	A	A	A
WAV	71255	35594	2	0.300	A	A	A	A
WAV	71257	35595	2	0.300	A	A	A	A
WAV	71265	35599	2	0.300	A	A	A	A
WAV	71267	35600	2	0.300	A	A	A	A
WAV	71289	35611	2	0.300	A	A	A	A
WAV	71291	35612	2	0.300	A	A	A	A
WAV	71313	35623	2	0.300	A	A	A	A
WAV	71325	35629	2	0.300	A	A	A	A
WAV	71355	35644	2	0.300	A	A	A	A
WAV	71367	35650	2	0.300	A	A	A	A
WAV	71369	35651	2	0.300	A	A	A	A
WAV	71371	35652	2	0.300	A	A	A	A
WAV	71467	35700	2	0.300	A	A	A	A
WAV	71483	35708	2	0.300	A	A	A	A
WAV	71569	35751	2	0.300	A	A	A	A
WAV	71577	35755	2	0.300	A	A	A	A
WAV	71579	35756	2	0.300	A	A	A	A
WAV	71581	35757	2	0.300	A	A	A	A
WAV	71605	35769	2	0.300	A	A	A	A
WAV	71607	35770	2	0.300	A	A	A	A
WAV	71609	35771	2	0.300	A	A	A	A
WAV	71611	35772	2	0.300	A	A	A	A
WAV	71613	35773	2	0.300	A	A	A	A
WAV	71615	35774	2	0.300	A	A	A	A
WAV	71617	35775	2	0.300	A	A	A	A
WAV	71619	35776	2	0.300	A	A	A	A
WAV	71621	35777	2	0.300	A	A	A	A
WAV	71623	35778	2	0.300	A	A	A	A
WAV	71825	35879	2	0.300	A	A	A	A
WAV	71839	35886	2	0.300	A	A	A	A
WAV	71845	35889	2	0.300	A	A	A	A
WAV	72183	36058	2	0.300	A	A	A	A
WAV	72185	36059	2	0.300	A	A	A	A
WAV	72187	36060	2	0.300	A	A	A	A
WAV	72189	36061	2	0.300	A	A	A	A
WAV	72191	36062	2	0.300	A	A	A	A
WAV	72193	36063	2	0.300	A	A	A	A
WAV	72195	36064	2	0.300	A	A	A	A
WAV	72197	36065	2	0.300	A	A	A	A
WAV	72199	36066	2	0.300	A	A	A	A
WAV	72201	36067	2	0.300	A	A	A	A
WAV	72203	36068	2	0.300	A	A	A	A
WAV	72205	36069	2	0.300	A	A	A	A
WAV	72207	36070	2	0.300	A	A	A	A
WAV	72209	36071	2	0.300	A	A	A	A
WAV	72211	36072	2	0.300	A	A	A	A
WAV	72215	36074	2	0.300	A	A	A	A
WAV	72217	36075	2	0.300	A	A	A	A
WAV	72219	36076	2	0.300	A	A	A	A
WAV	72221	36077	2	0.300	A	A	A	A
WAV	72223	36078	2	0.300	A	A	A	A
WAV	72225	36079	2	0.300	A	A	A	A
WAV	72227	36080	2	0.300	A	A	A	A
WAV	72229	36081	2	0.300	A	A	A	A
WAV	72231	36082	2	0.300	A	A	A	A
WAV	72233	36083	2	0.300	A	A	A	A
WAV	72235	36084	2	0.300	A	A	A	A
WAV	72237	36085	2	0.300	A	A	A	A
WAV	72239	36086	2	0.300	A	A	A	A
WAV	72241	36087	2	0.300	A	A	A	A
WAV	72243	36088	2	0.300	A	A	A	A
WAV	72245	36089	2	0.300	A	A	A	A
WAV	72247	36090	2	0.300	A	A	A	A
WAV	72249	36091	2	0.300	A	A	A	A
WAV	150173	74818	2	0.300	A	A	A	A