List users, ordered by accuracy of soccer match predictions

Question

I have a database filled with predictions of soccer matches. I need a solution to calculate the rankings from the database. There are 2 rankings: one for the entire season (playday=0) and one for each matchday (called playday in the code).

I have 3 tables:

1. matches
2. predictions
3. predictions_points

To give you a better insight in the database, here's some example data:

matches: contains soccer matches information.

+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+
| match_id | match_status |   match_datetime    | match_info | league_id | league_playday | home_team_id | home_team_score | away_team_id | away_team_score |
+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+
|        1 |            3 | 2016-07-29 20:30:00 |            |         1 |              1 |            1 |               0 |            2 |               2 |
|        2 |            3 | 2016-07-30 18:00:00 |            |         1 |              1 |            5 |               1 |            4 |               2 |
|        3 |            3 | 2016-07-30 20:00:00 |            |         1 |              1 |            3 |               1 |            6 |               0 |
|        4 |            3 | 2016-07-30 20:00:00 |            |         1 |              1 |            7 |               3 |            8 |               0 |
+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+

predictions: contains users predictions and the amount of points received per prediction.

+---------------+----------+---------+-----------------+-----------------+--------------------+
| prediction_id | match_id | user_id | home_team_score | away_team_score | predictions_points |
+---------------+----------+---------+-----------------+-----------------+--------------------+
|             1 |        1 |       1 |               0 |               1 |                  1 |
|             2 |        2 |       1 |               1 |               2 |                  3 |
|             3 |        3 |       1 |               2 |               0 |                  1 |
|             4 |        4 |       1 |               2 |               0 |                  1 |
|             5 |        1 |       2 |               0 |               2 |                  3 |
|             6 |        2 |       2 |               1 |               2 |                  3 |
|             7 |        3 |       2 |               1 |               0 |                  3 |
|             8 |        4 |       2 |               0 |               0 |                  0 |
+---------------+----------+---------+-----------------+-----------------+--------------------+

predictions_points contains the points per playday (or entire season when playday = 0) and the ranking (which we can not use for the query).

+-----------+---------+-----------+----------------+---------------------+---------------+
| points_id | user_id | league_id | league_playday | league_user_ranking | points_amount |
+-----------+---------+-----------+----------------+---------------------+---------------+
|         1 |       1 |         1 |              0 |                   2 |            51 |
|         2 |       2 |         1 |              0 |                   1 |            59 |
|         3 |       1 |         1 |              1 |                   2 |             6 |
|         4 |       2 |         1 |              1 |                   1 |             9 |
+-----------+---------+-----------+----------------+---------------------+---------------+

If there is a draw (in amount of points between users), I want to order them based on the amount of predictions they had 100% correct (they earned 1 point for a prediction with wrong score but correct win/draw/loss - and earned at least 3 points for a correct score).

(Please note that the league_user_ranking field from the predictions_points table gets updated based on the result set of this query. So we can not use it for the query.)

The following query works, but I feel like there's room for improvement:

     SELECT *, (
        SELECT COUNT(*) FROM predictions p
            INNER JOIN matches m
            ON m.match_id = p.match_id
        WHERE p.user_id=p_p.user_id 
        AND (m.league_playday=p_p.league_playday OR p_p.league_playday=0)
        AND p.prediction_points>=3
     ) AS correctpredictions_count
     FROM
     predictions_points p_p
     WHERE
     p_p.league_id=:league_id
     ORDER BY
     p_p.league_playday ASC, p_p.points_amount DESC, correctpredictions_count DESC

UPDATE/EDIT: I see that my question got bumped to the homepage. I am live-testing the code with 15 other soccer enthousiasts based on the results of the current Belgian soccer season. At the moment, this query takes about 10 seconds on a database with 3000 predictions (15 users, 8 matches per playday, 30 playdays) on a Raspberry Pi 3 running Raspbian Lite.

Expected result set:

    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+
    | points_id | user_id | league_id | league_playday | league_user_ranking | points_amount | correctpredictions_count |
    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+
    |         2 |       2 |         1 |              0 |                   1 |            59 |                        7 |
    |         1 |       1 |         1 |              0 |                   2 |            51 |                        6 |
    |         4 |       2 |         1 |              1 |                   1 |             9 |                        2 |
    |         3 |       1 |         1 |              1 |                   2 |             6 |                        1 |
    |         5 |       1 |         1 |              2 |                   1 |             7 |                        2 |
    |         6 |       2 |         1 |              2 |                   2 |             7 |                        1 |
    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+

Answer 1

Most of the joins currently happen in the sub-query within your SELECT statement. This is fine for a small number of records, but typically results in RBAR (row by agonizing row) performance.

For readability and to ensure we get that SET based performance we really want, consider the below query:

SELECT
    p_p.points_id,
    p_p.user_id,
    p_p.league_playday,
    pred_cnt.correctpredictions_count
FROM
    predictions_points p_p
JOIN
    (
        SELECT
            p.user_id,
            m.league_playday,
            COUNT(*) AS correctpredictions_count
        FROM 
            predictions p
        JOIN 
            matches m
                ON m.match_id = p.match_id
        WHERE
            #Greater than 3 points is a correct prediction
            p.points_amount >=3
        GROUP BY
            p.user_id,
            m.league_playday
    ) pred_cnt
        ON pred_cnt.user_id = p_p.user_id
        #Get correct predictions for individual play days and the whole season (league_playday=0)
        AND (pred_cnt.league_playday = p_p.league_playday OR p_p.league_playday=0) 
WHERE
    p_p.league_id=:league_id

Based on your above requirements, I believe you aggregate on the user_id and league_playday combination. The subquery in the JOIN will aggregate as a set first and then match back to prediction_points. I also added some comments in for the "magic numbers" just to make it more clear.

If you are just using the results to update your table, it looks like you have a primary key on points_id which would be all you need to return with the new count.