my current query looks something like this. I've been trying to optimize it but I got no luck so far.
My goal is to get the difference in stat_value between the current day and the previous day for every player and then order it.
The current code works fine, but feels unoptimized to me.
The ? values are filled in using Java.
SELECT t1.stat_value -
FROM leaderheadsplayersdata_daily t2
WHERE t2.player_id = t1.player_id
AND t2.day = ?
AND t2.stat_type = ?
) as sum
, (SELECT name
WHERE leaderheadsplayers.player_id = t1.player_id
FROM leaderheadsplayersdata_daily t1
WHERE day = ?
AND stat_type = ?
BY sum DESC LIMIT 100
CREATE TABLE IF NOT EXISTS `leaderheadsplayers`
(player_id INTEGER PRIMARY KEY AUTO_INCREMENT
,uuid VARCHAR(36) NOT NULL UNIQUE
,name VARCHAR(16) NOT NULL
) ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS leaderheadsplayersdata_daily
(player_id INTEGER NOT NULL
,stat_value DOUBLE NOT NULL
,stat_type VARCHAR(16) NOT NULL
,day INTEGER NOT NULL
,FOREIGN KEY (player_id) REFERENCES leaderheadsplayers(player_id) ON DELETE CASCADE
,PRIMARY KEY(player_id, stat_type, day)
) ENGINE = InnoDB
Because you only have 2 days, you can do a double join on the
It would look something like this:
SELECT p.player_id, p.name, (dc.stat_value-dp.statvalue) AS difference, dc.day, FROM leaderheadsplayers p JOIN leaderheadsplayersdata_daily dc ON p.player_id = dc.player_id JOIN leaderheadsplayersdata_daily dp ON p.player_id = dp.player_id and dp.day = (dc.day-1) WHERE dc.day = ?CURRENT_DAY? ORDER BY difference DESC
dc stands for "date current".
dp stands for "date previous".
p stands for "player".
For good performance, add indexes on the columns
player_id (in both tables) and
Sorry, I didn't test, have not ideea if I have syntax errors or stuff. Also, please replace
?CURRENT_DAY? with the actual day and add other conditions in
WHERE clause, as needed
ORDER BY difference DESC does not work, just do
ORDER BY (dc.stat_value-dp.statvalue) DESC.
You have a lot of subqueries, you look like you could take a look at some documentation about JOINs. They're really useful.