| dinamyc images [message #1710] |
Sun, 02 September 2007 07:25  |
 Leinad
Messages: 7
Registered: September 2007 |
Junior Member |
|
|
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)
[Updated on: Sun, 02 September 2007 07:26]
|
|
|
|
| Re: dinamyc images [message #1728 is a reply to message #1725 ] |
Mon, 03 September 2007 15:45  |
Leinad
Messages: 7
Registered: September 2007 |
Junior Member |
|
|
mmm...
check
http://www.mmorpgdesigners.com/tw/imagetest.php?xcoord=0& ;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
|
|
|
| Re: dinamyc images [message #1755 is a reply to message #1725 ] |
Wed, 05 September 2007 10:30 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
