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]