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 
      0
  ) as is_global 
FROM 
  cscart_product_option_variants as v 
  INNER JOIN cscart_product_option_variants_descriptions as d 
  INNER JOIN cscart_product_options 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 = 29992 
  AND o.option_id = 1607 
  AND v.status = 'A' 
ORDER BY 
  option_name ASC, 
  name ASC

Query time 0.00110

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", "c_status"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["option_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100
        },
        "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": 11,
          "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