abdoosh87

do you mean something like this ? : http://datatables.net/examples/data_sources/server_side.html http://datatables.net/development/server-side/php_mysql

hi, i've only got the following table in my database : | Field | Type | Null | Key | Default | Extra --------------------------------------------------------------------------------------------------------------------------------------- | id | int(20) | NO | PRI | NULL | auto_increment | | scr | int(20) | NO | | 0 | | | player_name | varchar(150) | NO | | NULL | | | location | varchar(5) | NO | | NULL | | | DateUpdated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | currently i've got an index on the id field . i'm dealing with 1 million records in my case . each player will have one record only in the database. i want to retrieve the global ranking of a specific player named : john with id : 682 as per the following example : Rank Id SCR DateUpdated--------------------------------------------- 15257 53264 62 2013-3-10 16:45:37 15258 3533 62 2013-3-10 16:45:37 15259 7283 62 2013-3-13 16:45:37 15260 386 61 2013-3-09 18:55:25 15261 78252 61 2013-3-10 13:33:21 15262 682 61 2013-3-10 16:45:37 <== this is our player 15263 9263 61 2013-3-10 16:45:37 15264 7263 61 2013-3-10 16:56:25 15265 7826 60 2013-3-10 12:26:37 15266 9276 60 2013-3-10 15:22:37 15267 932872 60 2013-3-13 11:45:37 the player appears in the middle with 5 players above and 5 players below Note that ranks is ordered by scr then DateUpdated this is my query which brings these results : SELECT id, scr, player_name, location, dateupdated, rank FROM ( select id,scr, player_name, location, dateupdated FROM scores WHERE id in ( select id from ( SELECT id FROM scores where id in ( select id from scores where scr >= (select scr from scores where id = 1140188) and id != 1140188 and id not in (select id from scores where scr in (select scr from scores where id = 1140188) and dateupdated >= (select dateupdated from scores where id = 1140188) ) order by scr asc, dateupdated ASC ) order by scr, dateupdated asc limit 0,5 ) as t union all select id from ( select id from scores where id = 1140188) as g union all select id from ( SELECT id FROM scores where id in ( select id from scores where scr <= (select scr from scores where id = 1140188) and id != 1140188 and id not in (select id from scores where scr in (select scr from scores where id = 1140188) and dateupdated < (select dateupdated from scores where id = 1140188)) order by scr asc, dateupdated ASC ) order by scr desc, dateupdated asc limit 0,5) as s ) order by scr desc, dateupdated ASC ) as A left join ( SELECT l.id as id2, @curRow := @curRow + 1 AS Rank FROM scores l JOIN (SELECT @curRow := 0) r ORDER BY scr DESC, dateupdated asc ) as B on A.id=B.id2; but this query takes around 8 sec on my local machine and it consumes a lot of resources, implementing this on a web service will end up in a disaster. can any one provide any hints here, even an entirely new query is welcomed .. please HELP !!!!!