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.


Comments

Post a Comment

Popular posts from this blog

magento2 - Magento 2 admin grid add filter to collection -

-
i created grid in admin xml ui components. need filter collection via url parameter , dont know how achieve that. tried inject requestinterface collection, filter didnt work. di.xml <virtualtype name="slidelistingdataprovider" type="magento\framework\view\element\uicomponent\dataprovider\dataprovider"> <arguments> <argument name="collection" xsi:type="object" shared="false">xxx\xxx\model\resourcemodel\grid\slide\collection</argument> <argument name="filterpool" xsi:type="object" shared="false">slidelistingfilterpool</argument> <!-- define new object filters --> </arguments> </virtualtype> <virtualtype name="slidelistingfilterpool" type="magento\framework\view\element\uicomponent\dataprovider\filterpool"> <arguments> <argument name="applier...
Read more

Android volley - avoid multiple requests of the same kind to the server? -

-
i using volley upload list of images server. happens within service. once image uploaded remove list. problem arises when internet connection breaks. when internet connection breaks , comes again thinking of adding images yet uploaded request queue. this can result in more 1 copy of same image saved on server request same image might have gone through before getting rseponse. how can address scenario? i'm not quite sure why you're using service schedule requests volley automatically runs it's request in seperate thread. nevertheless can listen both request results, successful , unsuccessful adding listeners. you can remove image list schedule request using volley. if succeeds can carry on desired, if fails can add again list. public static request getimageuploadrequest(final string image) { response.listener<t> responselistener = new response.listener<t>() { @override public void onresponse(t response...
Read more

Combining PHP Registration and Login into one class with multiple functions in one PHP file -

-
i'm trying combine registration, activation , login scripts php website backend script front end developer can pass variables different forms to. my question whether appropriate approach this. don't want have lot of php files different pieces of application developing. far have written following 2 functions login, register , activate user front end developer can call: <?php /** * created phpstorm. * user: karl * date: 26/07/2016 * time: 02:25 */ class users { function register_user($email, $password, $user_name) { $server_name = "localhost"; $u_name = "root"; $db_password = "root"; $db_name = "betamath_graspe"; //email notification variable $from_address="info@slack.com"; //registration form $msg_reg_user='username taken. please choose different username'; $msg_reg_email='email registered'; $msg_reg_active=...
Read more