Get Ranks Heavy Query Optimization - help needed

[abdoosh87]

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 !!!!!

 

[thescientist]

have you tried doing the sorting server side instead of on the way out of the database, and compared the performance?

[abdoosh87]

do you mean something like this ? :

 

http://datatables.net/examples/data_sources/server_side.html

 

http://datatables.net/development/server-side/php_mysql

[thescientist]

I just meant taking the $result and using something like usort on it, or some other loop / algorithm and then return that instead.