php - Caching big data, alternative query or other indexes? -

i'm problem, working on highscores, , highscores need make ranking based on skill experience , latest update time (to see got highest score first incase skill experience same).

the problem query wrote, takes 28 (skills) x 0,7 seconds create personal highscore page see rank on list. requesting in browser not doable, takes way long page load , need solution issue.

mysql version: 5.5.47

the query wrote:

select rank       (         select hs.playerid, (@rowid := @rowid + 1) rank                       (                 select hs.playerid                  highscores hs                 inner join overall o  on hs.playerid = o.playerid                  hs.skillid = ?                   , o.game_mode = ?                  order hs.skillexperience desc,                          hs.updatetime asc             ) highscore,         (select @rowid := 0) r     ) data data.playerid = ? 

as can see first have create whole resultset gives me full ranking game mode , skill, , have select rank based on playerid after that, problem cannot let query run untill finds result, because mysql doesn't offer such function, if i'd specifiy where data.playerid = ? in query above, give 1 result, meaning ranking 1 well.

the highscores table has 550k rows

what have tried storing resultset each skillid/gamemode combination in temp table json_encoded, tried storing on files, ended being quite slow well, because files huge , takes time process.

highscores table:

create table `highscores` (     `playerid` int(11) not null,     `skillid` int(10) not null,     `skilllevel` int(10) not null,     `skillexperience` int(10) not null,     `updatetime` bigint(20) not null,     primary key (`playerid`, `skillid`) ) collate='utf8_general_ci' engine=myisam; 

overall table has got 351k rows

overall table:

create table `overall` (     `playerid` int(11) not null,     `playername` varchar(50) not null,     `totallevel` int(10) not null,     `totalexperience` bigint(20) not null,     `updatetime` bigint(20) not null,     `game_mode` enum('regular','iron_man','iron_man_hardcore') not null default 'regular',     primary key (`playerid`, `playername`) ) collate='utf8_general_ci' engine=myisam; 

explain select result query:

does have solution me?

no useful index where

the last 2 lines of explain (#3 derived):

           hs.skillid = ?              , o.game_mode = ?  

since neither table has suitable index use where clause, optimizer decided table scan of 1 of them (overall), reach other (highscores). having 1 of these indexes help, @ least some:

highscores: index(skillid) overall: index(game_mode, ...) -- note index on low-cardinality enum useful. 

(more in minute.)

no useful index order by

the optimizer sometimes decides use index order by instead of where. but

            order hs.skillexperience desc,                      hs.updatetime      asc 

cannot use index, though both in same table. because desc , asc different. changing asc desc have impact on resultset, allow

index(skillexperience, updatetime) 

to used. still, may not optimal. (more in minute.)

covering index

another form of optimization build "covering index". index has columns select needs. query can performed entirely in index, without reaching on data. select in question innermost:

              ( select  hs.playerid                      highscores hs                     inner join  overall o on hs.playerid = o.playerid                      hs.skillid = ?                       ,  o.game_mode = ?                     order  hs.skillexperience desc, hs.updatetime asc                ) highscore, 

for hs: index(skillid, skillexperience, updatetime, playerid) "covering" , has important item (skillid, where) first.

for o: index(game_mode, playerid) "covering". again, game_mode must first.

if change order by desc , desc, add index hs: index(skillexperience, updatetime, skillid, playerid). first 2 columns must in order.

conclusion

it not obvious of indexes optimizer prefer. suggest add both , let choose.

i believe (1) innermost query consuming bulk of time, , (2) there nothing optimize in outer selects. so, leave recommendation.

much of covered in indexing cookbook.