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 (
    33810, 33809, 33808, 33807, 33781, 33780, 
    33952, 33798, 33793, 33792, 33791, 
    33790, 33789, 33788, 33787, 33786, 
    33785, 33784, 33783, 33782, 33763, 
    34355, 34354, 34353, 34352, 34351, 
    34350, 34349, 34348, 34347, 34346, 
    34345, 34344, 34343, 34342, 34341, 
    34340, 34339, 34338, 34337, 34336, 
    34335, 34334, 34333, 34332, 34331, 
    34303, 34302, 34301, 34300, 34299, 
    34298, 34297, 34296, 34295, 34294, 
    34293, 34292, 34291, 34290, 34289, 
    34288, 34287, 34286, 34285, 34284, 
    34176, 34175, 34174, 34173, 34172, 
    34171, 34170, 34169, 34168, 34167, 
    34166, 34165, 34164, 34163
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00305

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	67592	33763	1	0.000	A	A	A	A
MP3	67626	33780	1	0.000	A	A	A	A
MP3	67628	33781	1	0.000	A	A	A	A
MP3	67630	33782	1	0.000	A	A	A	A
MP3	67632	33783	1	0.000	A	A	A	A
MP3	67634	33784	1	0.000	A	A	A	A
MP3	67636	33785	1	0.000	A	A	A	A
MP3	67638	33786	1	0.000	A	A	A	A
MP3	67640	33787	1	0.000	A	A	A	A
MP3	67642	33788	1	0.000	A	A	A	A
MP3	67644	33789	1	0.000	A	A	A	A
MP3	67646	33790	1	0.000	A	A	A	A
MP3	67648	33791	1	0.000	A	A	A	A
MP3	67650	33792	1	0.000	A	A	A	A
MP3	67652	33793	1	0.000	A	A	A	A
MP3	67662	33798	1	0.000	A	A	A	A
MP3	67680	33807	1	0.000	A	A	A	A
MP3	67682	33808	1	0.000	A	A	A	A
MP3	67684	33809	1	0.000	A	A	A	A
MP3	67686	33810	1	0.000	A	A	A	A
MP3	67970	33952	1	0.000	A	A	A	A
MP3	68392	34163	1	0.000	A	A	A	A
MP3	68394	34164	1	0.000	A	A	A	A
MP3	68396	34165	1	0.000	A	A	A	A
MP3	68398	34166	1	0.000	A	A	A	A
MP3	68400	34167	1	0.000	A	A	A	A
MP3	68402	34168	1	0.000	A	A	A	A
MP3	68404	34169	1	0.000	A	A	A	A
MP3	68406	34170	1	0.000	A	A	A	A
MP3	68408	34171	1	0.000	A	A	A	A
MP3	68410	34172	1	0.000	A	A	A	A
MP3	68412	34173	1	0.000	A	A	A	A
MP3	68414	34174	1	0.000	A	A	A	A
MP3	68416	34175	1	0.000	A	A	A	A
MP3	68418	34176	1	0.000	A	A	A	A
MP3	68634	34284	1	0.000	A	A	A	A
MP3	68636	34285	1	0.000	A	A	A	A
MP3	68638	34286	1	0.000	A	A	A	A
MP3	68640	34287	1	0.000	A	A	A	A
MP3	68642	34288	1	0.000	A	A	A	A
MP3	68644	34289	1	0.000	A	A	A	A
MP3	68646	34290	1	0.000	A	A	A	A
MP3	68648	34291	1	0.000	A	A	A	A
MP3	68650	34292	1	0.000	A	A	A	A
MP3	68652	34293	1	0.000	A	A	A	A
MP3	68654	34294	1	0.000	A	A	A	A
MP3	68656	34295	1	0.000	A	A	A	A
MP3	68658	34296	1	0.000	A	A	A	A
MP3	68660	34297	1	0.000	A	A	A	A
MP3	68662	34298	1	0.000	A	A	A	A
MP3	68664	34299	1	0.000	A	A	A	A
MP3	68666	34300	1	0.000	A	A	A	A
MP3	68668	34301	1	0.000	A	A	A	A
MP3	68670	34302	1	0.000	A	A	A	A
MP3	68672	34303	1	0.000	A	A	A	A
MP3	68728	34331	1	0.000	A	A	A	A
MP3	68730	34332	1	0.000	A	A	A	A
MP3	68732	34333	1	0.000	A	A	A	A
MP3	68734	34334	1	0.000	A	A	A	A
MP3	68736	34335	1	0.000	A	A	A	A
MP3	68738	34336	1	0.000	A	A	A	A
MP3	68740	34337	1	0.000	A	A	A	A
MP3	68742	34338	1	0.000	A	A	A	A
MP3	68744	34339	1	0.000	A	A	A	A
MP3	68746	34340	1	0.000	A	A	A	A
MP3	68748	34341	1	0.000	A	A	A	A
MP3	68750	34342	1	0.000	A	A	A	A
MP3	68752	34343	1	0.000	A	A	A	A
MP3	68754	34344	1	0.000	A	A	A	A
MP3	68756	34345	1	0.000	A	A	A	A
MP3	68758	34346	1	0.000	A	A	A	A
MP3	68760	34347	1	0.000	A	A	A	A
MP3	68762	34348	1	0.000	A	A	A	A
MP3	68764	34349	1	0.000	A	A	A	A
MP3	68766	34350	1	0.000	A	A	A	A
MP3	68768	34351	1	0.000	A	A	A	A
MP3	68770	34352	1	0.000	A	A	A	A
MP3	68772	34353	1	0.000	A	A	A	A
MP3	68774	34354	1	0.000	A	A	A	A
MP3	68776	34355	1	0.000	A	A	A	A
WAV	67593	33763	2	0.300	A	A	A	A
WAV	67627	33780	2	0.300	A	A	A	A
WAV	67629	33781	2	0.300	A	A	A	A
WAV	67631	33782	2	0.300	A	A	A	A
WAV	67633	33783	2	0.300	A	A	A	A
WAV	67635	33784	2	0.300	A	A	A	A
WAV	67637	33785	2	0.300	A	A	A	A
WAV	67639	33786	2	0.300	A	A	A	A
WAV	67641	33787	2	0.300	A	A	A	A
WAV	67643	33788	2	0.300	A	A	A	A
WAV	67645	33789	2	0.300	A	A	A	A
WAV	67647	33790	2	0.300	A	A	A	A
WAV	67649	33791	2	0.300	A	A	A	A
WAV	67651	33792	2	0.300	A	A	A	A
WAV	67653	33793	2	0.300	A	A	A	A
WAV	67663	33798	2	0.300	A	A	A	A
WAV	67681	33807	2	0.300	A	A	A	A
WAV	67683	33808	2	0.300	A	A	A	A
WAV	67685	33809	2	0.300	A	A	A	A
WAV	67687	33810	2	0.300	A	A	A	A
WAV	67971	33952	2	0.300	A	A	A	A
WAV	68393	34163	2	0.300	A	A	A	A
WAV	68395	34164	2	0.300	A	A	A	A
WAV	68397	34165	2	0.300	A	A	A	A
WAV	68399	34166	2	0.300	A	A	A	A
WAV	68401	34167	2	0.300	A	A	A	A
WAV	68403	34168	2	0.300	A	A	A	A
WAV	68405	34169	2	0.300	A	A	A	A
WAV	68407	34170	2	0.300	A	A	A	A
WAV	68409	34171	2	0.300	A	A	A	A
WAV	68411	34172	2	0.300	A	A	A	A
WAV	68413	34173	2	0.300	A	A	A	A
WAV	68415	34174	2	0.300	A	A	A	A
WAV	68417	34175	2	0.300	A	A	A	A
WAV	68419	34176	2	0.300	A	A	A	A
WAV	68635	34284	2	0.300	A	A	A	A
WAV	68637	34285	2	0.300	A	A	A	A
WAV	68639	34286	2	0.300	A	A	A	A
WAV	68641	34287	2	0.300	A	A	A	A
WAV	68643	34288	2	0.300	A	A	A	A
WAV	68645	34289	2	0.300	A	A	A	A
WAV	68647	34290	2	0.300	A	A	A	A
WAV	68649	34291	2	0.300	A	A	A	A
WAV	68651	34292	2	0.300	A	A	A	A
WAV	68653	34293	2	0.300	A	A	A	A
WAV	68655	34294	2	0.300	A	A	A	A
WAV	68657	34295	2	0.300	A	A	A	A
WAV	68659	34296	2	0.300	A	A	A	A
WAV	68661	34297	2	0.300	A	A	A	A
WAV	68663	34298	2	0.300	A	A	A	A
WAV	68665	34299	2	0.300	A	A	A	A
WAV	68667	34300	2	0.300	A	A	A	A
WAV	68669	34301	2	0.300	A	A	A	A
WAV	68671	34302	2	0.300	A	A	A	A
WAV	68673	34303	2	0.300	A	A	A	A
WAV	68729	34331	2	0.300	A	A	A	A
WAV	68731	34332	2	0.300	A	A	A	A
WAV	68733	34333	2	0.300	A	A	A	A
WAV	68735	34334	2	0.300	A	A	A	A
WAV	68737	34335	2	0.300	A	A	A	A
WAV	68739	34336	2	0.300	A	A	A	A
WAV	68741	34337	2	0.300	A	A	A	A
WAV	68743	34338	2	0.300	A	A	A	A
WAV	68745	34339	2	0.300	A	A	A	A
WAV	68747	34340	2	0.300	A	A	A	A
WAV	68749	34341	2	0.300	A	A	A	A
WAV	68751	34342	2	0.300	A	A	A	A
WAV	68753	34343	2	0.300	A	A	A	A
WAV	68755	34344	2	0.300	A	A	A	A
WAV	68757	34345	2	0.300	A	A	A	A
WAV	68759	34346	2	0.300	A	A	A	A
WAV	68761	34347	2	0.300	A	A	A	A
WAV	68763	34348	2	0.300	A	A	A	A
WAV	68765	34349	2	0.300	A	A	A	A
WAV	68767	34350	2	0.300	A	A	A	A
WAV	68769	34351	2	0.300	A	A	A	A
WAV	68771	34352	2	0.300	A	A	A	A
WAV	68773	34353	2	0.300	A	A	A	A
WAV	68775	34354	2	0.300	A	A	A	A
WAV	68777	34355	2	0.300	A	A	A	A