Performance considerations for SUB QUERY, LIMIT and ORDER BY when using MySQL

Question

Objective: Select 10 users whose ongoing games is < 5.

Approach 1: Do everything within MySQL

Select users (LIMIT 10) and check n_ongoingGames for each user

Approach 2: SQL + result-set analysis

Get records for say 30 users. For each record, go to db again and check for n_ongoingGames. Assuming probability of (n_ongoingGames > 5) for each user as 50%, we'll need to check for 20 records.

-- Approach 1: Do everything within MySQL
-- --------------------------------------
SELECT u.*
FROM USERS u
WHERE 
    ( -- n_ongoingGames for user < 5
        (
            SELECT COUNT(gameID) AS n_ongoingGames
            FROM games g
            WHERE 
                (g.player1_userID = u.userID) OR 
                (g.player2_userID = u.userID) OR
                (g.player3_userID = u.userID) OR
                ...
                ...
                ...
                (g.player10_userID = u.userID)
        ) < 5
    ) AND
    (u.cash_balance > 500)
ORDER BY u.user_rating
LIMIT 10;


-- Approach 2: SQL + result-set analysis
-- -------------------------------------
SELECT u.*
FROM USERS u
WHERE
    (u.cash_balance > 500)
ORDER BY u.user_rating
LIMIT 30;

Questions:

In approach 1, does MySQL check (n_ongoingGames for user < 5) for every user in the users table ?
Which approach is faster ?
Does it make a difference (on which approach is faster) if only 1 user is needed instead of 10 (LIMIT 1)

Thanks.

Let's say, this application facilitates group discussion for 10 users. And each user can at best be part of 5 ongoing discussions. — Kaya Toast, Oct 20 '15 at 17:16
I'm sorry, I don't understand what's a group discussion, put in perspective with your model description. Also, this does not comply with the initial requirement `Select users whose ongoing games is < 5.`, which does not mention any user limit. I'm convinced this limit is not relevant, from the sql query perspective. If there's a quantity constraint on the number of rows, they should be ensured by the application or by actual database constraints. — Sebas, Oct 20 '15 at 17:21
10 users are given a topic to discuss/debate. A user can participate in multiple debates/discussions simultaneously. But n_simultaneous_debates < 5. You're right - I modified the objective statement accordingly and added 10 to it. — Kaya Toast, Oct 20 '15 at 17:28
Holy crap, 10 user fields? If you're really concerned about performance, please normalize all that ASAP — Sebas, Oct 20 '15 at 17:46
Yup, duly noted. I wanted to keep the question simple with only 2 tables. — Kaya Toast, Oct 20 '15 at 17:49

score 3 · Accepted Answer · answered Oct 20 '15 at 17:29

I would suggest the following, assuming a user cannot be both players:

SELECT u.*
FROM USERS u
WHERE u.cash_balance > 500 AND
      ((SELECT COUNT(*) AS n_ongoingGames
        FROM games g
        WHERE g.player1_userID = u.userID
       ) +
       (SELECT COUNT(*) AS n_ongoingGames
        FROM games g
        WHERE g.player2_userID = u.userID
       )
      ) < 5
ORDER BY u.user_rating
LIMIT 10;

With the following indexes: games(player1_userID) and games(player2_userID). You also want an index on users; one possibility is users(user_rating, cash_balance), but I don't think MySQL will be smart enough to scan the table in order using the index. You might have to settle for users(cash_balance).

The indexes on games() mean that the counts can be satisfied in the indexes. This should be a particularly fast type of counting. If you remove the condition on cash balance, then an index on users(user_rating) should make the query quite fast.

Apologies for not being clear earlier. A game consists of 10 users. For a new game, we want to select 10 users, and we're checking among existing games whether the (n_ongoingGames < 5). The potential user may be playing as player1, player2 ... player10 in any of the existing games. — Kaya Toast, Oct 20 '15 at 17:39

Performance considerations for SUB QUERY, LIMIT and ORDER BY when using MySQL

1 Answers1