Skip to main content
Code Review

Return to Revisions

1 of 2
Jan
Jan
  • 71
  • 4

How can I optimize this SQL ranking generator query?

So 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? Thanks

Jan
  • 71
  • 4