I suspect this query has died due to the volume in the tables.  Running a query that returns all userids 24000 times will eventually cause performance issues.

At some point you your tables grow to the point they don't fit in memory (very fast access) and you end up reading data from disk (very slow queries for most queries. This is a sudden switch. One query I wrote went from sub-second to five minutes when this happened. If you can compact the tables, this may resolve the condition for a while.  Adding more memory may help.  (Even if your database doesn't have enough buffer space, the O/S may buffer the data for you.)

This is how I formatted the query to understand it.

    SELECT userid 
    FROM   users 
    LEFT OUTER JOIN routes 
    ON     routes.user_id = users.id 
    LEFT OUTER JOIN aliases 
    ON     aliases.route_id = routes.id 
    WHERE  aliases.name = 'xxxx' 
    AND    domain_id = 8;

If you are searching for aliases that match the aliases.name, it would help to index that column.

Based on the LEFT OUTER JOINS, you should get the userid for all users or all users with domain_id = 8. As I read this query it is a long way of writing:

    SELECT userid 
    FROM   users;

Or

    SELECT userid 
    from   users
    WHERE  domain_id = 8;


However I expect you want the user id when the alias and domain match.

    SELECT userid 
    FROM   users 
    JOIN   routes 
    ON     routes.user_id = users.id 
    JOIN   aliases 
    ON     aliases.route_id = routes.id 
    WHERE  aliases.name = 'xxxx' 
    AND    domain_id = 8;


If you are looking for the userid matching the aliases.name value, it would help to index aliases.name.