How can I optimize this SQL ranking generator query?

Question

I have a World Cup fixture app where people can guess the results of the matches. Everything is working great, but each time I update the ranking, it takes 20 minutes for just 3000 users. So there must be something really wrong with this.

These are the tables being used:

Users

CREATE TABLE `users` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `id_facebook` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `first_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `last_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `gender` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
 `locale` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
 `country` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `location` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `timezone` int(11) NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `fb_token` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`),
 KEY `id_facebook` (`id_facebook`)
) ENGINE=InnoDB AUTO_INCREMENT=3478 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Matches

CREATE TABLE `matches` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `venue` varchar(100) COLLATE utf8_bin NOT NULL,
 `stage` varchar(100) COLLATE utf8_bin NOT NULL,
 `teamA` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT '',
 `teamB` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT '',
 `goalsAinit` int(11) DEFAULT NULL,
 `goalsBinit` int(11) DEFAULT NULL,
 `goalsAadded` int(11) DEFAULT NULL,
 `goalsBadded` int(11) DEFAULT NULL,
 `penaltiesA` int(11) DEFAULT NULL,
 `penaltiesB` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Predictions

CREATE TABLE `predictions` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `id_user` int(11) unsigned DEFAULT NULL,
 `id_match` int(11) unsigned DEFAULT NULL,
 `goalsAinit` int(11) DEFAULT NULL,
 `goalsBinit` int(11) DEFAULT NULL,
 `goalsAadded` int(11) DEFAULT NULL,
 `goalsBadded` int(11) DEFAULT NULL,
 `penaltiesA` int(11) DEFAULT NULL,
 `penaltiesB` int(11) DEFAULT NULL,
 `points` int(11) DEFAULT '60',
 `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=81499 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

So, each time a match ends, I update the record on the matches table and then run this query so that I have an updated ranking:

INSERT INTO ranking
SELECT rank, id_user, id_facebook, iso, country, first_name, last_name, name, exact, correct, points, total_predictions
FROM (
SELECT @rank:=@rank+1 AS rank, id_user, id_facebook, first_name, last_name, name, iso, country, exact,correct, points, total_predictions
FROM (
SELECT u.id as id_user, u.id_facebook, u.first_name, u.last_name, u.name, iso, u.country,
 SUM(IF((p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit), 1, 0)) AS exact,
 SUM(IF(((p.goalsAinit > p.goalsBinit AND m.goalsAinit > m.goalsBinit)
 OR (p.goalsAinit < p.goalsBinit AND m.goalsAinit < m.goalsBinit)
 OR (p.goalsAinit = p.goalsBinit AND m.goalsAinit = m.goalsBinit))
 AND !(p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit), 1, 0)) AS correct,
 SUM(IF((p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit), (20*3), IF((p.goalsAinit > p.goalsBinit AND m.goalsAinit > m.goalsBinit)
 OR (p.goalsAinit < p.goalsBinit AND m.goalsAinit < m.goalsBinit)
 OR (p.goalsAinit = p.goalsBinit AND m.goalsAinit = m.goalsBinit), ROUND(20*1.5), IF(p.goalsAinit IS NOT NULL AND p.goalsBinit IS NOT NULL AND m.goalsAinit IS NOT NULL AND m.goalsBinit IS NOT NULL,10,0)))) AS points,
 SUM(p.goalsAinit IS NOT NULL AND p.goalsBinit IS NOT NULL) AS total_predictions
 FROM users u
 LEFT JOIN predictions p ON u.id = p.id_user
 LEFT JOIN matches m ON m.id = p.id_match
 LEFT JOIN flags f ON u.country = f.country
 GROUP BY u.id ORDER BY points DESC, exact DESC, correct DESC, total_predictions DESC, id_user
) AS rankings, (SELECT @rank:=0) AS r
) AS overall_rankings

How can I improve this so it doesn't take so long?

Answer 1

You are doing a huge amount of work in the inner rankings table.

The most important observations here are:

1. you are doing a 'group by' on only the user_id column, but the actual columns that are not aggregated are many. This is a bad practice... each column that is not aggregated, should be part of the group-by clause.
2. you are doing multiple left-joins in there too, and left-joins have a significant impact on the possible optimizations the database can do.
3. you are joining to the user table when calculating the scores, but all you need is the id_user from the predictions table.

Also, I presume the 'iso' column comes from the 'flags' table?

I messed with your query, you will see what I have done. I believe it will work, but it may just return null-values for users with no predictions... but, this allows me to use an inner-join between predictions and matches. If there's a problem with nulls in the data column, then you can solve that with judicious use of IsNull conditions on the outer selects. I also reformatted the if-conditions so I could make sense of them. You can collapse it all down again if you need.

Without further ado....

Edit revised my query to make the ranking system better.....

 INSERT INTO ranking
 SELECT @rank:=IFNULL(@rank,0)+1 AS rank,
 id as id_user,
 id_facebook,
 first_name,
 last_name,
 name,
 iso,
 country,
 exact,
 correct, 
 points, 
 total_predictions
 FROM users u
 LEFT JOIN flags f ON u.country = f.country
 LEFT JOIN (
 SELECT p.id_user as id_user,
 SUM( IF((p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit), 1, 0)) AS exact,
 SUM( IF( ( (p.goalsAinit > p.goalsBinit AND m.goalsAinit > m.goalsBinit)
 OR (p.goalsAinit < p.goalsBinit AND m.goalsAinit < m.goalsBinit)
 OR (p.goalsAinit = p.goalsBinit AND m.goalsAinit = m.goalsBinit))
 AND !(p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit), 1, 0)) AS correct,
 SUM(IF( (p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit),
 (20*3),
 IF( (p.goalsAinit > p.goalsBinit AND m.goalsAinit > m.goalsBinit)
 OR (p.goalsAinit < p.goalsBinit AND m.goalsAinit < m.goalsBinit)
 OR (p.goalsAinit = p.goalsBinit AND m.goalsAinit = m.goalsBinit),
 ROUND(20*1.5),
 IF(p.goalsAinit IS NOT NULL AND p.goalsBinit IS NOT NULL AND m.goalsAinit IS NOT NULL AND m.goalsBinit IS NOT NULL,10,0)))) AS points,
 SUM(p.goalsAinit IS NOT NULL AND p.goalsBinit IS NOT NULL) AS total_predictions
 FROM predictions p
 INNER JOIN matches m ON m.id = p.id_match
 GROUP BY p.id_user) as userstats on u.id = userstats.id_user
 ORDER BY points DESC, exact DESC, correct DESC, total_predictions DESC, id

---

Edit: previous version of answer code

INSERT INTO ranking
SELECT rank, id_user, id_facebook, iso, country, first_name, last_name, name, exact, correct, points, total_predictions
FROM (SELECT @rank:=0) AS r,
 (
 SELECT @rank:=@rank+1 AS rank,
 id as id_user,
 id_facebook,
 first_name,
 last_name,
 name,
 iso,
 country,
 exact,
 correct, 
 points, 
 total_predictions
 FROM users u
 LEFT JOIN flags f ON u.country = f.country
 LEFT JOIN (
 SELECT p.id_user as id_user,
 SUM( IF((p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit), 1, 0)) AS exact,
 SUM( IF( ( (p.goalsAinit > p.goalsBinit AND m.goalsAinit > m.goalsBinit)
 OR (p.goalsAinit < p.goalsBinit AND m.goalsAinit < m.goalsBinit)
 OR (p.goalsAinit = p.goalsBinit AND m.goalsAinit = m.goalsBinit))
 AND !(p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit), 1, 0)) AS correct,
 SUM(IF( (p.goalsAinit = m.goalsAinit AND p.goalsBinit = m.goalsBinit),
 (20*3),
 IF( (p.goalsAinit > p.goalsBinit AND m.goalsAinit > m.goalsBinit)
 OR (p.goalsAinit < p.goalsBinit AND m.goalsAinit < m.goalsBinit)
 OR (p.goalsAinit = p.goalsBinit AND m.goalsAinit = m.goalsBinit),
 ROUND(20*1.5),
 IF(p.goalsAinit IS NOT NULL AND p.goalsBinit IS NOT NULL AND m.goalsAinit IS NOT NULL AND m.goalsBinit IS NOT NULL,10,0)))) AS points,
 SUM(p.goalsAinit IS NOT NULL AND p.goalsBinit IS NOT NULL) AS total_predictions
 FROM predictions p
 INNER JOIN matches m ON m.id = p.id_match
 GROUP BY p.id_user) as userstats on u.id = userstats.id_user
 ORDER BY points DESC, exact DESC, correct DESC, total_predictions DESC, id
) AS overall_rankings;

• rolfl: @Jan - I edited my answer to be even simpler.
• Jan: Hey! Thanks a lot for your answer. Using your query it seems as the order of the users is correct, but for some reason the rank column is showing random numbers. And they keep going up with every repeatition of the query. It looks as if it's creating that number based on the user_id BEFORE generating the rank.
• rolfl: @Jan - I wonder if the @rank needs to be reset as part of the query.... the (select @rank = 0) is required (and remove the IFNULL(...))... and the @rank persists from one run to the next.