dinamyc images

[Leinad]

Hello guys, I just found out this page and seems amazing

well, I’m stuck with a problem…
I have this 1mill row table (yeah, I know 1 mill is not too much…:/) well, it is when you need to create dinamic images for 200 users at the same time (this images has to load in 0.5 seconds MAX!!)

ok, using one big table with 1M rows was an incorrect approach (5 seconds minimun per image) so I divided my table into 100 tables…ok this is good, but now I have to select data from up to 9 tables (center table, south, north, east, west and all the corners) (that’s a search on 90k rows)

For every table I have to do a query like:

SELECT villages0_0.village_id, villages0_0.ycoord, villages0_0.xcoord, villages0_0.terrain, villages0_0.user_id
FROM villages0_0
WHERE (
(
villages0_0.xcoord >49 && villages0_0.xcoord <149
) && ( villages0_0.ycoord >49 && villages0_0.ycoord <149 ) && ( villages0_0.terrain != ‘plain’ || villages0_0.user_id !=0 )
)

which is good (0.015 seconds to check on 10k rows)
BUT problem is that I have to join those queries…so that means 9 queries like that (using union) and well, I’m about to add the 6th query to the union and the time has increased to 0.1128 seg to 1 second…

so this is my question…
can you recommend a faster way to do my queries?? (all the tables uses the exact same fields)
OR
do you think I should split it into 400 tables instead of 100? (which could be good as well, as the image only shows a 50x50 tiles at one time (with a max search of 22250 rows)

regards and thanks in advance

pd. oh as well, may you tell me if it’s ok to use this
villages0_0.village_id, villages0_0.ycoord, villages0_0.xcoord, villages0_0.terrain, villages0_0.user_id

instead of this

which would be faster (my table has about 50 fields)

[Peter]

I do not really understand you.
creating dynamic images meaning your queries have to be fast, this does not mean you have to have a lot of tables. There are other ways to optimize queries to.

Plus do not forget about caching - for images it is very improtant.

[Leinad]

mmm…

check

http://www.mmorpgdesigners.com/tw/imagetest.php?xcoord=0&amp ;ycoord=0

for a better grasp on what I’m trying to do…just play with xcoord and ycoord )

as you can see, it shows “NON-PLAIN” terrain (darker green), occupied villages (dark red)

it also shows all your villages on yellow and your current village on white.

it has to show, your tribe villages, the villages you’re in peace with, the ones you’re in war at, the abandoned villages, and a whole bunch of other, which means more and more queries…

so if anybody has deal with this type of job before, please lemme know )

[Peter]

You should use one query here to return you basically all non empty cells returning some flag indicating their content.

It may not be set up such way in your current schema but you need to build such table, may be as extra table if your data frequently changes and table is performance is important.

Also using GIS may help here.