Desiging a multiplayer 2d map

[magester]

Hi guys, the company I work for currently has a online game that has a 2d map. Currently it is being handled by ajax then the server does the logic returns the data to the browser and updates the map.

 

Here is where my question comes in, the player has a view box that is X spaces in the X and Y directions around the ship. Also the only ships that will show up have more then half hit points and is not type 9 and are at sea (ID -1). This query helps return the attackable ships "around" this ship.

 

Here is my query:

 

SELECT owner_id,name,type FROM ships WHERE map_x < 44 AND map_x > 24 AND map_y < 18 AND map_y > 10 AND hp >= (hp_max / 2) AND type<>9 AND city = -1

 

The problem is that the ships table is very large, this query is not using indexes and from what I can tell if you have ranges or between in your where code index will not be used. Now I know this is a query that is pretty slow since it is showing up on the server logs constantly. I need to know if anyone knows of a better way to optimize the above query or if anyone can provide a better data structure for a table of this type as players are constantly moving and that table updates X and Y every time a player moves.

 

 

[kickstart]

Hi

 

Quick look and I don't find a reason that using between or < / > should stop it using indexes.

 

Suspect the calcultion (hp_max / 2) might cause issues

 

All the best

 

Keith

[magester]

I tried removing all but map_x in query using < it says using where in the explain, map_x is indexed.

[kickstart]

Hi

 

Can you post the explains?

 

All the best

 

Keith

[magester]

SELECT owner_id,name,type FROM ships WHERE map_x < 44 AND map_x > 24 AND map_y < 18 AND map_y > 10 AND hp >= (hp_max / 2) AND type<>9 AND city = -1

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE ships ALL map_x,map_y,type,range,range_where NULL NULL NULL 9681 Using where

 

EXPLAIN SELECT owner_id, name,

TYPE FROM ships

WHERE map_x <44

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE ships ALL map_x,range,range_where NULL NULL NULL 9681 Using where

 

 

Tried indexing multiple things

 

Indexes: Documentation    Keyname  Type  Cardinality  Action  Field

PRIMARY PRIMARY 9681  Edit Drop id

owner INDEX 9681  Edit Drop owner_id

navigation INDEX 4  Edit Drop navigation

navigation_max

map_x INDEX 68  Edit Drop map_x

map_y INDEX 40  Edit Drop map_y

type INDEX 10  Edit Drop type

range INDEX 4840  Edit Drop map_x

map_y

hp

type

range_where INDEX 1936  Edit Drop map_x

map_y

 

[kickstart]

Hi

 

It is recognising the keys but just ignoring them.

 

Only thing I can think of is that none of the keys alone will restrict the data brought back by enough for MySQL to think they are worthwhile. Might be worth trying to have an index over the type and city columns which would hopefully reduce the volume by enough, and possibly have a seperate column for hp_max / 2 (duplicates data, but might be worthwhile to avoid constantly redoing that calculation).

 

All th ebest

 

Keith

[fenway]

There are all sorts of sneaky things you can do with map coordinates... but let's try something easier first.

 

Is "hp_max" a column? Why no index on "hp"?

 

How restrictive is the city clause?  How restrictive is the hp clause? That is, of the ~10K records, how many match?