optimization - Optimizing MySQL query across multiple rows -

my current query looks this. i've been trying optimize got no luck far. goal difference in stat_value between current day , previous day every player , order it. current code works fine, feels unoptimized me. ? values filled in using java.

select t1.stat_value -         (select stat_value            leaderheadsplayersdata_daily t2           t2.player_id = t1.player_id             , t2.day = ?             , t2.stat_type = ?           limit 1        ) sum      , (select name            leaderheadsplayers           leaderheadsplayers.player_id = t1.player_id           limit 1        )      leaderheadsplayersdata_daily t1     day = ?       , stat_type = ?     order        sum desc limit 100 

table structure:

this main table containing player id , username.

create table if not exists `leaderheadsplayers`  (player_id integer primary key auto_increment ,uuid varchar(36) not null unique ,name varchar(16) not null ,last_join datetime ) engine = innodb 

this table containing daily data.

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 

thanks in advance

because have 2 days, can double join on leaderheadsplayersdata_daily table.

it this:

select     p.player_id,     p.name,     (dc.stat_value-dp.statvalue) difference,     dc.day, leaderheadsplayers p join leaderheadsplayersdata_daily dc on p.player_id = dc.player_id join leaderheadsplayersdata_daily dp on p.player_id = dp.player_id , dp.day = (dc.day-1) dc.day = ?current_day? order difference desc 

dc stands "date current". dp stands "date previous". p stands "player".

for performance, add indexes on columns player_id (in both tables) , day.

sorry, didn't test, have not ideea if have syntax errors or stuff. also, please replace ?current_day? actual day , add other conditions in where clause, needed

if order difference desc not work, order (dc.stat_value-dp.statvalue) desc.

you have lot of subqueries, take @ documentation joins. they're useful.