Question RE: Query Efficiency and Indexes

[kaiousama]

I'm working on an an online game currently, and I've been spending the past few days trimming down my SQL queries to reduce load times, and I'm now at the point where I'm not sure if there are any further corners to cut.

 

My main concern is on a map page.  To load a page that has the map on it, 6 queries are run.  Each subsequent movement across the map uses 9 queries, which are as follows (not actual syntax with regard to variable usage):

 

[*]User authentication: SELECT user_id,player_id,platform FROM users WHERE user_id=$user_id AND password=$password

[*]Load the player info into an array:  SELECT * FROM players WHERE player_id=$player_id

[*]Load the player's team into an an array: SELECT * FROM teams WHERE player_id=$player_id

[*]Check the cached responses to make sure it isn't a double-click on a link:  SELECT cache_data FROM page_cache WHERE player_id=$player_id AND request_id=$request

[*]Load the map tiles into an array:  SELECT tile_id,url,travel FROM tiles

[*]Check if the player's movement is allowed: SELECT area_id,tile FROM maps WHERE x=$x and y=$y

[*]If movement is allowed: UPDATE players SET x=$x, y=$y, area=$area WHERE player_id=$player_info['player_id']

[*]Load the 9x9 map from the current position:  SELECT tile,x,y FROM maps WHERE x>=$x-4 AND x<= $+4 AND y>=$y-4 AND y<=$y+4

[*]Check for any events that occur at the players position: SELECT encounter_id,rate FROM encounters WHERE area_id=$area

 

Indexes are as follows:

Users has a two-column index on user_id and platform

Players has the primary key set to player_id

Teams has the primary key set to team_id, and an index on player_id

Page_Cache has a two-column index on player_id and request_id

Tiles has the primary key set to tile_id

Maps has a two-column index on x and y

Encounters has an index on area_id

 

 

Would anybody have ideas regarding the changes on indices, or to the queries?  For a single user, the entire PHP page tends to render in 0.001 - 0.004 seconds.  My concern is how high that might get if I have 1,000 people moving around simultaneously.

[BioBob]

If you want to test your efficiency, load it up with some dummy data, like you said about 1,000 users, so load it up with about a thousand rows of just junk.

 

You could also try doing some JOINING on a couple of those first fields to again reduce the number of queries, but not sure if that will speed it up at all...

[fenway]

You can check EXPLAIN output to ensure proper index usage... but worry about scalability issues later.