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 (
    41510, 41509, 41508, 41461, 41460, 41459, 
    41458, 41457, 41456, 41455, 41454, 
    41453, 41452, 41451, 41450, 41449, 
    41448, 41447, 41446, 41445, 41220, 
    41167, 41166, 41164, 41411, 41410, 
    41409, 41408, 41488, 41487, 41486, 
    41485, 41484, 41483, 41482, 41481, 
    41480, 41479, 41478, 41477, 41476, 
    41475, 41474, 41473, 41472, 41471, 
    41470, 41469, 41468, 41467, 41466, 
    41465, 41464, 41218, 41217, 41216, 
    41215, 41214, 41213, 41212, 41211, 
    41210, 41134, 41133, 41132, 41131, 
    41118, 41077, 41072, 41071, 41038, 
    41296, 41295, 41294, 41293, 40950, 
    40932, 41179, 41178, 40960
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00249

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	81930	40932	1	0.000	A	A	A	A
MP3	81966	40950	1	0.000	A	A	A	A
MP3	81986	40960	1	0.000	A	A	A	A
MP3	82142	41038	1	0.000	A	A	A	A
MP3	82208	41071	1	0.000	A	A	A	A
MP3	82210	41072	1	0.000	A	A	A	A
MP3	82220	41077	1	0.000	A	A	A	A
MP3	82302	41118	1	0.000	A	A	A	A
MP3	82328	41131	1	0.000	A	A	A	A
MP3	82330	41132	1	0.000	A	A	A	A
MP3	82332	41133	1	0.000	A	A	A	A
MP3	82334	41134	1	0.000	A	A	A	A
MP3	82394	41164	1	0.000	A	A	A	A
MP3	82398	41166	1	0.000	A	A	A	A
MP3	82400	41167	1	0.000	A	A	A	A
MP3	82422	41178	1	0.000	A	A	A	A
MP3	82424	41179	1	0.000	A	A	A	A
MP3	82486	41210	1	0.000	A	A	A	A
MP3	82488	41211	1	0.000	A	A	A	A
MP3	82490	41212	1	0.000	A	A	A	A
MP3	82492	41213	1	0.000	A	A	A	A
MP3	82494	41214	1	0.000	A	A	A	A
MP3	82496	41215	1	0.000	A	A	A	A
MP3	82498	41216	1	0.000	A	A	A	A
MP3	82500	41217	1	0.000	A	A	A	A
MP3	82502	41218	1	0.000	A	A	A	A
MP3	82506	41220	1	0.000	A	A	A	A
MP3	82652	41293	1	0.000	A	A	A	A
MP3	82654	41294	1	0.000	A	A	A	A
MP3	82656	41295	1	0.000	A	A	A	A
MP3	82658	41296	1	0.000	A	A	A	A
MP3	82882	41408	1	0.000	A	A	A	A
MP3	82884	41409	1	0.000	A	A	A	A
MP3	82886	41410	1	0.000	A	A	A	A
MP3	82888	41411	1	0.000	A	A	A	A
MP3	82956	41445	1	0.000	A	A	A	A
MP3	82958	41446	1	0.000	A	A	A	A
MP3	82960	41447	1	0.000	A	A	A	A
MP3	82962	41448	1	0.000	A	A	A	A
MP3	82964	41449	1	0.000	A	A	A	A
MP3	82966	41450	1	0.000	A	A	A	A
MP3	82968	41451	1	0.000	A	A	A	A
MP3	82970	41452	1	0.000	A	A	A	A
MP3	82972	41453	1	0.000	A	A	A	A
MP3	82974	41454	1	0.000	A	A	A	A
MP3	82976	41455	1	0.000	A	A	A	A
MP3	82978	41456	1	0.000	A	A	A	A
MP3	82980	41457	1	0.000	A	A	A	A
MP3	82982	41458	1	0.000	A	A	A	A
MP3	82984	41459	1	0.000	A	A	A	A
MP3	82986	41460	1	0.000	A	A	A	A
MP3	82988	41461	1	0.000	A	A	A	A
MP3	82994	41464	1	0.000	A	A	A	A
MP3	82996	41465	1	0.000	A	A	A	A
MP3	82998	41466	1	0.000	A	A	A	A
MP3	83000	41467	1	0.000	A	A	A	A
MP3	83002	41468	1	0.000	A	A	A	A
MP3	83004	41469	1	0.000	A	A	A	A
MP3	83006	41470	1	0.000	A	A	A	A
MP3	83008	41471	1	0.000	A	A	A	A
MP3	83010	41472	1	0.000	A	A	A	A
MP3	83012	41473	1	0.000	A	A	A	A
MP3	83014	41474	1	0.000	A	A	A	A
MP3	83016	41475	1	0.000	A	A	A	A
MP3	83018	41476	1	0.000	A	A	A	A
MP3	83020	41477	1	0.000	A	A	A	A
MP3	83022	41478	1	0.000	A	A	A	A
MP3	83024	41479	1	0.000	A	A	A	A
MP3	83026	41480	1	0.000	A	A	A	A
MP3	83028	41481	1	0.000	A	A	A	A
MP3	83030	41482	1	0.000	A	A	A	A
MP3	83032	41483	1	0.000	A	A	A	A
MP3	83034	41484	1	0.000	A	A	A	A
MP3	83036	41485	1	0.000	A	A	A	A
MP3	83038	41486	1	0.000	A	A	A	A
MP3	83040	41487	1	0.000	A	A	A	A
MP3	83042	41488	1	0.000	A	A	A	A
MP3	83082	41508	1	0.000	A	A	A	A
MP3	83084	41509	1	0.000	A	A	A	A
MP3	83086	41510	1	0.000	A	A	A	A
WAV	81931	40932	2	0.300	A	A	A	A
WAV	81967	40950	2	0.300	A	A	A	A
WAV	81987	40960	2	0.300	A	A	A	A
WAV	82143	41038	2	0.300	A	A	A	A
WAV	82209	41071	2	0.300	A	A	A	A
WAV	82211	41072	2	0.300	A	A	A	A
WAV	82221	41077	2	0.300	A	A	A	A
WAV	82303	41118	2	0.300	A	A	A	A
WAV	82329	41131	2	0.300	A	A	A	A
WAV	82331	41132	2	0.300	A	A	A	A
WAV	82333	41133	2	0.300	A	A	A	A
WAV	82335	41134	2	0.300	A	A	A	A
WAV	82395	41164	2	0.300	A	A	A	A
WAV	82399	41166	2	0.300	A	A	A	A
WAV	82401	41167	2	0.300	A	A	A	A
WAV	82423	41178	2	0.300	A	A	A	A
WAV	82425	41179	2	0.300	A	A	A	A
WAV	82487	41210	2	0.300	A	A	A	A
WAV	82489	41211	2	0.300	A	A	A	A
WAV	82491	41212	2	0.300	A	A	A	A
WAV	82493	41213	2	0.300	A	A	A	A
WAV	82495	41214	2	0.300	A	A	A	A
WAV	82497	41215	2	0.300	A	A	A	A
WAV	82499	41216	2	0.300	A	A	A	A
WAV	82501	41217	2	0.300	A	A	A	A
WAV	82503	41218	2	0.300	A	A	A	A
WAV	82507	41220	2	0.300	A	A	A	A
WAV	82653	41293	2	0.300	A	A	A	A
WAV	82655	41294	2	0.300	A	A	A	A
WAV	82657	41295	2	0.300	A	A	A	A
WAV	82659	41296	2	0.300	A	A	A	A
WAV	82883	41408	2	0.300	A	A	A	A
WAV	82885	41409	2	0.300	A	A	A	A
WAV	82887	41410	2	0.300	A	A	A	A
WAV	82889	41411	2	0.300	A	A	A	A
WAV	82957	41445	2	0.300	A	A	A	A
WAV	82959	41446	2	0.300	A	A	A	A
WAV	82961	41447	2	0.300	A	A	A	A
WAV	82963	41448	2	0.300	A	A	A	A
WAV	82965	41449	2	0.300	A	A	A	A
WAV	82967	41450	2	0.300	A	A	A	A
WAV	82969	41451	2	0.300	A	A	A	A
WAV	82971	41452	2	0.300	A	A	A	A
WAV	82973	41453	2	0.300	A	A	A	A
WAV	82975	41454	2	0.300	A	A	A	A
WAV	82977	41455	2	0.300	A	A	A	A
WAV	82979	41456	2	0.300	A	A	A	A
WAV	82981	41457	2	0.300	A	A	A	A
WAV	82983	41458	2	0.300	A	A	A	A
WAV	82985	41459	2	0.300	A	A	A	A
WAV	82987	41460	2	0.300	A	A	A	A
WAV	82989	41461	2	0.300	A	A	A	A
WAV	82995	41464	2	0.300	A	A	A	A
WAV	82997	41465	2	0.300	A	A	A	A
WAV	82999	41466	2	0.300	A	A	A	A
WAV	83001	41467	2	0.300	A	A	A	A
WAV	83003	41468	2	0.300	A	A	A	A
WAV	83005	41469	2	0.300	A	A	A	A
WAV	83007	41470	2	0.300	A	A	A	A
WAV	83009	41471	2	0.300	A	A	A	A
WAV	83011	41472	2	0.300	A	A	A	A
WAV	83013	41473	2	0.300	A	A	A	A
WAV	83015	41474	2	0.300	A	A	A	A
WAV	83017	41475	2	0.300	A	A	A	A
WAV	83019	41476	2	0.300	A	A	A	A
WAV	83021	41477	2	0.300	A	A	A	A
WAV	83023	41478	2	0.300	A	A	A	A
WAV	83025	41479	2	0.300	A	A	A	A
WAV	83027	41480	2	0.300	A	A	A	A
WAV	83029	41481	2	0.300	A	A	A	A
WAV	83031	41482	2	0.300	A	A	A	A
WAV	83033	41483	2	0.300	A	A	A	A
WAV	83035	41484	2	0.300	A	A	A	A
WAV	83037	41485	2	0.300	A	A	A	A
WAV	83039	41486	2	0.300	A	A	A	A
WAV	83041	41487	2	0.300	A	A	A	A
WAV	83043	41488	2	0.300	A	A	A	A
WAV	83083	41508	2	0.300	A	A	A	A
WAV	83085	41509	2	0.300	A	A	A	A
WAV	83087	41510	2	0.300	A	A	A	A