MySQL Ranking Query and LEFT JOIN

Question

I have a database of users and facebook_accounts, which belong to users. Users are "ranked" by how many points they have in relation to other users: this is done with an embedded SELECT statement that counts all users with more points than the user.

The database has ~10k users. The following SQL query takes MySQL ~0.16s to fulfill:

SELECT
    *, (SELECT (COUNT(*) + 1)
            FROM users AS UserHigher
            WHERE UserHigher.points > User.points
       ) AS rank
FROM
    users AS User
ORDER BY
    User.points DESC, User.created ASC
LIMIT 0, 30

However, adding a LEFT JOIN to also retrieve the user's facebook_account hangs MySQL:

SELECT
    *, (SELECT (COUNT(*) + 1)
            FROM users AS UserHigher
            WHERE UserHigher.points > User.points
       ) AS rank
FROM
    users AS User
LEFT JOIN
    facebook_accounts AS FacebookAccount
        ON (FacebookAccount.user_id = User.id)
ORDER BY
    User.points DESC, User.created ASC
LIMIT 0, 30

I understand that the COUNT() select method for ranking users is somewhat inefficient, but this is the most reliable method I have come across. What I don't understand is why a simple LEFT JOIN destroys an otherwise reasonable query, when it appears to be completely separate from the ranking SELECT statement.

Any advice?

Answer 1

My guess is that the original query does the ordering first and only executes the rank 30 times. The second query is too complicated for MySQL to detect this optimization.

The following might help:

select *
from (SELECT *, (SELECT (COUNT(*) + 1)
                 FROM users AS UserHigher
                 WHERE UserHigher.points > User.points
                ) AS rank
      FROM users AS User
      ORDER BY User.points DESC, User.created ASC
      LIMIT 0, 30
     ) t join
     facebook_accounts AS FacebookAccount
     ON (FacebookAccount.user_id = User.id)
order by points desc, created asc