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]
