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 (
    44976, 44975, 44974, 44973, 44972, 44971, 
    44970, 44969, 45706, 44939, 44938, 
    44917, 44916, 44570, 44902, 44864, 
    44920, 44919, 44918, 44849, 44845, 
    44844, 44843, 44539, 44727, 44696, 
    44695, 44694, 44693, 44692, 44691, 
    44690, 44689, 44688, 44687, 44686, 
    44685, 44534, 44533, 44532, 44531, 
    44735, 44734, 44729, 44726, 44506, 
    44469, 83142, 83141, 44507, 44501, 
    44500, 44492, 44617, 44616, 44590, 
    44589, 44588, 44414, 44577, 44574, 
    44424, 44423, 44422, 44421, 44420, 
    44682, 44681, 44680, 44679, 44678, 
    44677, 44676, 44675, 44674, 44673, 
    44672, 44671, 44670, 44669
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00256

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	88894	44414	1	0.000	A	A	A	A
MP3	88906	44420	1	0.000	A	A	A	A
MP3	88908	44421	1	0.000	A	A	A	A
MP3	88910	44422	1	0.000	A	A	A	A
MP3	88912	44423	1	0.000	A	A	A	A
MP3	88914	44424	1	0.000	A	A	A	A
MP3	89004	44469	1	0.000	A	A	A	A
MP3	89050	44492	1	0.000	A	A	A	A
MP3	89066	44500	1	0.000	A	A	A	A
MP3	89068	44501	1	0.000	A	A	A	A
MP3	89078	44506	1	0.000	A	A	A	A
MP3	89080	44507	1	0.000	A	A	A	A
MP3	89128	44531	1	0.000	A	A	A	A
MP3	89130	44532	1	0.000	A	A	A	A
MP3	89132	44533	1	0.000	A	A	A	A
MP3	89134	44534	1	0.000	A	A	A	A
MP3	89144	44539	1	0.000	A	A	A	A
MP3	89206	44570	1	0.000	A	A	A	A
MP3	89214	44574	1	0.000	A	A	A	A
MP3	89220	44577	1	0.000	A	A	A	A
MP3	89242	44588	1	0.000	A	A	A	A
MP3	89244	44589	1	0.000	A	A	A	A
MP3	89246	44590	1	0.000	A	A	A	A
MP3	89298	44616	1	0.000	A	A	A	A
MP3	89300	44617	1	0.000	A	A	A	A
MP3	89404	44669	1	0.000	A	A	A	A
MP3	89406	44670	1	0.000	A	A	A	A
MP3	89408	44671	1	0.000	A	A	A	A
MP3	89410	44672	1	0.000	A	A	A	A
MP3	89412	44673	1	0.000	A	A	A	A
MP3	89414	44674	1	0.000	A	A	A	A
MP3	89416	44675	1	0.000	A	A	A	A
MP3	89418	44676	1	0.000	A	A	A	A
MP3	89420	44677	1	0.000	A	A	A	A
MP3	89422	44678	1	0.000	A	A	A	A
MP3	89424	44679	1	0.000	A	A	A	A
MP3	89426	44680	1	0.000	A	A	A	A
MP3	89428	44681	1	0.000	A	A	A	A
MP3	89430	44682	1	0.000	A	A	A	A
MP3	89436	44685	1	0.000	A	A	A	A
MP3	89438	44686	1	0.000	A	A	A	A
MP3	89440	44687	1	0.000	A	A	A	A
MP3	89442	44688	1	0.000	A	A	A	A
MP3	89444	44689	1	0.000	A	A	A	A
MP3	89446	44690	1	0.000	A	A	A	A
MP3	89448	44691	1	0.000	A	A	A	A
MP3	89450	44692	1	0.000	A	A	A	A
MP3	89452	44693	1	0.000	A	A	A	A
MP3	89454	44694	1	0.000	A	A	A	A
MP3	89456	44695	1	0.000	A	A	A	A
MP3	89458	44696	1	0.000	A	A	A	A
MP3	89518	44726	1	0.000	A	A	A	A
MP3	89520	44727	1	0.000	A	A	A	A
MP3	89524	44729	1	0.000	A	A	A	A
MP3	89534	44734	1	0.000	A	A	A	A
MP3	89536	44735	1	0.000	A	A	A	A
MP3	89752	44843	1	0.000	A	A	A	A
MP3	89754	44844	1	0.000	A	A	A	A
MP3	89756	44845	1	0.000	A	A	A	A
MP3	89764	44849	1	0.000	A	A	A	A
MP3	89794	44864	1	0.000	A	A	A	A
MP3	89870	44902	1	0.000	A	A	A	A
MP3	89898	44916	1	0.000	A	A	A	A
MP3	89900	44917	1	0.000	A	A	A	A
MP3	89902	44918	1	0.000	A	A	A	A
MP3	89904	44919	1	0.000	A	A	A	A
MP3	89906	44920	1	0.000	A	A	A	A
MP3	89942	44938	1	0.000	A	A	A	A
MP3	89944	44939	1	0.000	A	A	A	A
MP3	90004	44969	1	0.000	A	A	A	A
MP3	90006	44970	1	0.000	A	A	A	A
MP3	90008	44971	1	0.000	A	A	A	A
MP3	90010	44972	1	0.000	A	A	A	A
MP3	90012	44973	1	0.000	A	A	A	A
MP3	90014	44974	1	0.000	A	A	A	A
MP3	90016	44975	1	0.000	A	A	A	A
MP3	90018	44976	1	0.000	A	A	A	A
MP3	91478	45706	1	0.000	A	A	A	A
MP3	166818	83141	1	0.000	A	A	A	A
MP3	166820	83142	1	0.000	A	A	A	A
WAV	88895	44414	2	0.300	A	A	A	A
WAV	88907	44420	2	0.300	A	A	A	A
WAV	88909	44421	2	0.300	A	A	A	A
WAV	88911	44422	2	0.300	A	A	A	A
WAV	88913	44423	2	0.300	A	A	A	A
WAV	88915	44424	2	0.300	A	A	A	A
WAV	89005	44469	2	0.300	A	A	A	A
WAV	89051	44492	2	0.300	A	A	A	A
WAV	89067	44500	2	0.300	A	A	A	A
WAV	89069	44501	2	0.300	A	A	A	A
WAV	89079	44506	2	0.300	A	A	A	A
WAV	89081	44507	2	0.300	A	A	A	A
WAV	89129	44531	2	0.300	A	A	A	A
WAV	89131	44532	2	0.300	A	A	A	A
WAV	89133	44533	2	0.300	A	A	A	A
WAV	89135	44534	2	0.300	A	A	A	A
WAV	89145	44539	2	0.300	A	A	A	A
WAV	89207	44570	2	0.300	A	A	A	A
WAV	89215	44574	2	0.300	A	A	A	A
WAV	89221	44577	2	0.300	A	A	A	A
WAV	89243	44588	2	0.300	A	A	A	A
WAV	89245	44589	2	0.300	A	A	A	A
WAV	89247	44590	2	0.300	A	A	A	A
WAV	89299	44616	2	0.300	A	A	A	A
WAV	89301	44617	2	0.300	A	A	A	A
WAV	89405	44669	2	0.300	A	A	A	A
WAV	89407	44670	2	0.300	A	A	A	A
WAV	89409	44671	2	0.300	A	A	A	A
WAV	89411	44672	2	0.300	A	A	A	A
WAV	89413	44673	2	0.300	A	A	A	A
WAV	89415	44674	2	0.300	A	A	A	A
WAV	89417	44675	2	0.300	A	A	A	A
WAV	89419	44676	2	0.300	A	A	A	A
WAV	89421	44677	2	0.300	A	A	A	A
WAV	89423	44678	2	0.300	A	A	A	A
WAV	89425	44679	2	0.300	A	A	A	A
WAV	89427	44680	2	0.300	A	A	A	A
WAV	89429	44681	2	0.300	A	A	A	A
WAV	89431	44682	2	0.300	A	A	A	A
WAV	89437	44685	2	0.300	A	A	A	A
WAV	89439	44686	2	0.300	A	A	A	A
WAV	89441	44687	2	0.300	A	A	A	A
WAV	89443	44688	2	0.300	A	A	A	A
WAV	89445	44689	2	0.300	A	A	A	A
WAV	89447	44690	2	0.300	A	A	A	A
WAV	89449	44691	2	0.300	A	A	A	A
WAV	89451	44692	2	0.300	A	A	A	A
WAV	89453	44693	2	0.300	A	A	A	A
WAV	89455	44694	2	0.300	A	A	A	A
WAV	89457	44695	2	0.300	A	A	A	A
WAV	89459	44696	2	0.300	A	A	A	A
WAV	89519	44726	2	0.300	A	A	A	A
WAV	89521	44727	2	0.300	A	A	A	A
WAV	89525	44729	2	0.300	A	A	A	A
WAV	89535	44734	2	0.300	A	A	A	A
WAV	89537	44735	2	0.300	A	A	A	A
WAV	89753	44843	2	0.300	A	A	A	A
WAV	89755	44844	2	0.300	A	A	A	A
WAV	89757	44845	2	0.300	A	A	A	A
WAV	89765	44849	2	0.300	A	A	A	A
WAV	89795	44864	2	0.300	A	A	A	A
WAV	89871	44902	2	0.300	A	A	A	A
WAV	89899	44916	2	0.300	A	A	A	A
WAV	89901	44917	2	0.300	A	A	A	A
WAV	89903	44918	2	0.300	A	A	A	A
WAV	89905	44919	2	0.300	A	A	A	A
WAV	89907	44920	2	0.300	A	A	A	A
WAV	89943	44938	2	0.300	A	A	A	A
WAV	89945	44939	2	0.300	A	A	A	A
WAV	90005	44969	2	0.300	A	A	A	A
WAV	90007	44970	2	0.300	A	A	A	A
WAV	90009	44971	2	0.300	A	A	A	A
WAV	90011	44972	2	0.300	A	A	A	A
WAV	90013	44973	2	0.300	A	A	A	A
WAV	90015	44974	2	0.300	A	A	A	A
WAV	90017	44975	2	0.300	A	A	A	A
WAV	90019	44976	2	0.300	A	A	A	A
WAV	91479	45706	2	0.300	A	A	A	A
WAV	166819	83141	2	0.300	A	A	A	A
WAV	166821	83142	2	0.300	A	A	A	A