Sandbox

SELECT 
  v.variant_id as id, 
  d.variant_name as name, 
  o.option_id as option_id, 
  i.option_name as option_name, 
  v.position as position, 
  v.nav_skus, 
  v.image_size, 
  v.modifier, 
  v.modifier_type, 
  v.commercial_modifier, 
  v.commercial_dn, 
  v.extra_details, 
  v.vertical_position, 
  v.separator_label, 
  v.lead_time, 
  v.base_cost, 
  v.same_as_option, 
  v.price_overrides, 
  (
    select 
      1
  ) as is_global 
FROM 
  cscart_product_option_variants as v 
  INNER JOIN cscart_product_option_variants_descriptions as d 
  INNER JOIN cscart_product_global_option_links as o 
  INNER JOIN cscart_product_options_descriptions as i 
  INNER JOIN cscart_products as p ON v.variant_id = d.variant_id 
  AND o.option_id = i.option_id 
  AND o.option_id = v.option_id 
  AND o.product_id = p.product_id 
WHERE 
  p.product_id = 62246 
  AND o.option_id = 22247 
  AND v.status = 'A' 
ORDER BY 
  option_name ASC, 
  name ASC

Query time 0.00071

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "i.option_name, d.variant_name",
      "temporary_table": {
        "table": {
          "table_name": "o",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "product_id"],
          "key": "PRIMARY",
          "key_length": "6",
          "used_key_parts": ["option_id", "product_id"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        },
        "table": {
          "table_name": "p",
          "access_type": "const",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100,
          "using_index": true
        },
        "table": {
          "table_name": "i",
          "access_type": "ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["option_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100
        },
        "table": {
          "table_name": "v",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "option_id", "option_id_2", "status"],
          "key": "option_id",
          "key_length": "6",
          "used_key_parts": ["option_id", "status"],
          "ref": ["const", "const"],
          "rows": 16,
          "filtered": 100,
          "index_condition": "v.`status` = 'A'"
        },
        "table": {
          "table_name": "d",
          "access_type": "ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["variant_id"],
          "ref": ["cscart_migrate.v.variant_id"],
          "rows": 1,
          "filtered": 100
        }
      }
    }
  }
}

Result

id	name	option_id	option_name	position	nav_skus	image_size	modifier	modifier_type	commercial_modifier	extra_details	separator_label	is_global
219252	Elva LED Tala Lamp - 28ELVA	22247	Bulbs	75		4	28.000	A	28.00	420 Lumens		1
219257	Enno LED Tala Lamp - 28ENNO	22247	Bulbs	100		3	90.000	A	90.00	540 Lumens		1
219250	Globe LED Tala Lamp - 28GLOB	22247	Bulbs	65		4	15.000	A	15.00	600 Lumens		1
242973	LED Amber ST19 - 31-4.5FSST19DIM/820/A	22247	Bulbs	15		4	13.000	A	13.00	250 Lumens		1
219239	LED Clear ST19 - 31-4.5FSST19DIM/821	22247	Bulbs	10		4	13.000	A	13.00	250 Lumens		1
219238	LED Silver Bowl G25 - 31-4.5FG25DIM/827/SB/R	22247	Bulbs	5		4	19.000	A	19.00	400 Lumens	Single LED Bulbs	1
219241	LED Smoky ST19 - 31-4.5FSST19DIM/840/S	22247	Bulbs	20		4	14.000	A	14.00	150 Lumens		1
219249	Lurra LED Tala Lamp - 28LURA	22247	Bulbs	60		4	13.000	A	13.00	210 Lumens	Single Porcelain LED Tala Lamps	1
219256	Noma LED Tala Lamp - 28NOMA	22247	Bulbs	95		3	90.000	A	90.00	540 Lumens		1
219237	None	22247	Bulbs	3		4	0.000	A	0.00			1
219254	Oblo LED Tala Lamp - 28OBLO	22247	Bulbs	85		3	90.000	A	90.00	540 Lumens		1
219253	Oval LED Tala Lamp - 28OVAL	22247	Bulbs	80		3	90.000	A	90.00	540 Lumens		1
220320	Please Select	22247	Bulbs	0	skip	4	0.000	A	0.00			1
219255	Porcelain III LED Tala Lamp - 28PORC-III	22247	Bulbs	90		3	38.000	A	38.00	540 Lumens		1
219258	Voronoi I LED Tala Lamp - 28VORN-I	22247	Bulbs	105		3	93.000	A	93.00	100 Lumens		1
219259	Voronoi II LED Tala Lamp - 28VORN-II	22247	Bulbs	110		2	145.000	A	145.00	150 Lumens		1