Brent, Thanks for the reply. I knew that MySQL has spatial index, although reading it quickly, I wasn't sure that this is for me. But maybe I'll read it again in more details.
On your second suggestion, i.e. to do INSERT a bit at a time, how can I automate this ? Also, does it matter if the tables are using MyISAM or InnoDB ? Cheers, Chris ----- Original Message ---- From: Brent Baisley <[EMAIL PROTECTED]> To: Chris Prakoso <[EMAIL PROTECTED]>; mysql@lists.mysql.com Sent: Wednesday, 23 May, 2007 1:30:50 PM Subject: Re: Help on selecting a View with 3 Billions rows ! I think what you want to do is look into creating a spacial index on the raw data. Then just searching on that index will allow you to judge distances between things. You won't need to create a table with every single possible combination. There are quite a few examples in the manual and online. But, if you really want to go that route, do a little bit at a time instead of all at once. It will go quicker and you'll be able to judge the prgress. For example, join 1 record from one table with the 65K records from the other and insert that into the combination table. Then grab the second record, etc. Do that 54K times. You'll be surprised at how quickly it goes. ----- Original Message ----- From: "Chris Prakoso" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Tuesday, May 22, 2007 6:43 PM Subject: Help on selecting a View with 3 Billions rows ! > Hi all, > > > > > I am having a big performance performance problem here, and I was wondering > if you could give me some advise here. > > > I have 2 big static tables, one with 65K rows, the other with 54K rows. > I need to do a calculation for each combination of these two table > rows. > > > So what I did was to create a view like so: > > > > > select > > > `c`.`TRACT` AS `TRACT`, > > > `c`.`LNG` AS `tlng`, > > > `c`.`LAT` AS `tlat`, > > > `p`.`NAME` AS `name`, > > > `p`.`LNG` AS `lng`, > > > `p`.`LAT` AS `lat`, > > > `Calc_Distance`(`c`.`LAT`,`c`.`LNG`,`p`.`LAT`,`p`.`LNG`) AS `distance` > > > from > > > (`tracts` `c` join `parks` `p`); > > > > > This give me a view with more than 3,500,000,000 rows ! > > > > Now, the second part of this exercise is to grab only a set or rows, where > the distance is less than 50. > > > So, I thought I would create another view like so: > > > > > select > > > `t`.`TRACT` AS `TRACT`, > > > `t`.`tlng` AS `tlng`, > > > `t`.`tlat` AS `tlat`, > > > `t`.`name` AS `name`, > > > `t`.`lng` AS `lng`, > > > `t`.`lat` AS `lat`, > > > `t`.`distance` AS `distance` > > > from > > > `tractparkdistance` `t` > > > where > > > (`t`.`distance` < 50); > > > > > tractparkdisctance is the name of the view. > > > > > But opening this view takes 'a lot of time' ! I just couldn't wait for it. > > > > So, I though I would try to export this to an external file via > SELECT INTO, and re-import the resulting file back to a new table. > > > > > So I did like so: > > > > > select * > > > into outfile "park_distances" > > > from tractparkdistance > > > where distance < 50; > > > > Running this statement took more than 12 hours, and still counting > until I killed the process. So far it has produced an 800 MB file. > > > Moreover, I still need to do a Mean calculation from that 'limited' set of > data, and still do more calculations. > > > Next try, I using INSERT INTO SELECT like this: > > insert into park_distance_radius50s(tract,tlng,tlat,name,lng,lat,distance) > select tract,tlng,tlat,name,lng,lat,distance > from tractparkdistance > where distance < 50 > > This was running a very long time as well. > > > > I think I'm out of my depth here. Anybody has any idea on this ? > > > > > Thanks very much in advance ! > > > > > Regards, > > > Chris > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
