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]
