huge performance hit from "query string for node subscriptions"

I'm no DBA but it seems the CAST function is causing problems here :

$query = "SELECT s.*, f.value AS nid, n.type AS node_type, n.title FROM {notifications} s 
    INNER JOIN {notifications_fields} f ON s.sid = f.sid LEFT JOIN {node} n ON f.value = CAST(n.nid AS CHAR)
    WHERE s.uid = %d AND s.type = 'thread' AND s.event_type = 'node' AND s.conditions = 1 AND f.field = 'nid'
    ORDER BY node_type, n.title";

When run on a site with 20,000 + nodes and 13,000+ notifications on a user with a lot of notifications it causes the db to slow to the point of stopping. However if you remove the CAST() function its still a slow query, but it runs and finishes.