SELECT 
  v.variant_id, 
  v.modifier, 
  v.nav_skus, 
  v.option_id, 
  v.same_as_option, 
  v.price_overrides, 
  d.variant_name as 'variant_name', 
  n.option_name as 'option_name', 
  o.position as 'option_position', 
  v.commercial_modifier, 
  v.commercial_dn 
FROM 
  cscart_product_option_variants as v 
  LEFT JOIN cscart_product_options as o ON v.option_id = o.option_id 
  LEFT JOIN cscart_product_options_descriptions as n ON n.option_id = o.option_id 
  LEFT JOIN cscart_product_option_variants_descriptions as d ON v.variant_id = d.variant_id 
WHERE 
  v.variant_id = 240943 
ORDER BY 
  o.position DESC

Query time 0.00032

JSON explain

{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "table": {
      "table_name": "v",
      "access_type": "const",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["variant_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "o",
      "access_type": "const",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["option_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "n",
      "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": "d",
      "access_type": "ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["variant_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    }
  }
}

Result