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:UsersCREATE 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;MatchesCREATE 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;PredictionsCREATE 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 rankingSELECT rank, id_user, id_facebook, iso, country, first_name, last_name, name, exact, correct, points, total_predictionsFROM (SELECT @rank:=@rank+1 AS rank, id_user, id_facebook, first_name, last_name, name, iso, country, exact,correct, points, total_predictionsFROM (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_rankingsHow can I improve this so it doesn't take so long?
How can I optimize this SQL ranking generator query?
optimization;sql;mysql