Im back again with additional questions 
I currently have a map table which holds each square on a map. The data held in the map table is as follows:
map_id (PK auto-inc), x, y, type, terrain, fogofwar, name, pop, u_id
I was populating the database with values of random terrain types, but then I've decided that when a user registers, he must be placed on a specific setup (map terrain type with surrounding terrain types for resource fields)
Soo I fill the database as normal using random then use another loop that updates the table 5 times (middle square set to one terrain type and 4 surrounding to the others)...
With the current map size the amount of updates required for a map table with 250k rows is 50k updates...
Populating the table with 250k rows took around 1minute but now updating it 50k times is taking longer than 20minutes :/
My UPDATE query involves randomly picking an X, Y then updating the stuff around it. sooo UPDATE map WHERE x = 5 AND y = 5... and then the 4 around it...
Should I be indexing both the X + Y to speed the query up? or perhaps move some of the other fields out of there that are likely to change such as map_name, map_fow, map_population, u_id etc for extra effiency? thanks
I currently have a map table which holds each square on a map. The data held in the map table is as follows:
map_id (PK auto-inc), x, y, type, terrain, fogofwar, name, pop, u_id
I was populating the database with values of random terrain types, but then I've decided that when a user registers, he must be placed on a specific setup (map terrain type with surrounding terrain types for resource fields)
Soo I fill the database as normal using random then use another loop that updates the table 5 times (middle square set to one terrain type and 4 surrounding to the others)...
With the current map size the amount of updates required for a map table with 250k rows is 50k updates...
Populating the table with 250k rows took around 1minute but now updating it 50k times is taking longer than 20minutes :/
My UPDATE query involves randomly picking an X, Y then updating the stuff around it. sooo UPDATE map WHERE x = 5 AND y = 5... and then the 4 around it...
Should I be indexing both the X + Y to speed the query up? or perhaps move some of the other fields out of there that are likely to change such as map_name, map_fow, map_population, u_id etc for extra effiency? thanks
sadly they didn't really teach us much about database enhancement at college