Page 1 of 1

using a table of 5 million entries to perform a search!!

Posted: Mon Oct 03, 2005 2:28 am
by robster
Hi all,

I am doing a postcode search facility on my site, where a user can say:

Code: Select all

Show me all car brand X users that live Y distance or less kilomoters from location Z.
or

Code: Select all

Show me all Ford drivers that live 100 or less kilometers from Sydney, Australia.


I posted this thread originally to ask where I might come across the data required: viewtopic.php?p=206105#206105

I found that the data could be sourced from these folk: http://www.findmap.com.au/pcdist.html


What I am considering, is taking the data, and using it for my search... Now to my question (soon ;)).

Basically, in their database they sell, there are 5 million records that look like this:

Code: Select all

Postode Source, Postcode Destination, Kilometers difference (as crow flies)
2000,2006,3
2000,2007,2
2000,2008,3
2000,2009,1
2000,2010,2
2000,2011,1
2000,2015,5
2000,2016,3
2000,2017,4
2000,2018,7
2000,2019,10
etc,etc,etc

The question
If I were to purchase and stick this into my mySQL database (all 5 million records!!!):
A) Will it slow down general usage of the system
B) Will using it to try and whittle down search results be slow?

That's the first question I presume, if they answer positively, then I'd like to discuss this further :)

Thanks so much, I appreciate any feedback very much.

Rob

Posted: Mon Oct 03, 2005 3:29 am
by dbevfat
I think 5 mio records shouldn't slow down the system too much, provided the table is properly indexed.

Re: using a table of 5 million entries to perform a search!!

Posted: Mon Oct 03, 2005 3:31 am
by Weirdan
robster wrote: The question
If I were to purchase and stick this into my mySQL database (all 5 million records!!!):
A) Will it slow down general usage of the system
Putting the data into your db once won't slow down your system. Querying it, that's another story...
B) Will using it to try and whittle down search results be slow?
Judging from the schema, it should be pretty efficient to query the table for exact matches, i.e.

Code: Select all

select
   offer.id
from
   car_offers as offer
inner join
   distances as distance
on offer.zip=distance.endpoint
where
   distance.startpoint = $userzip
   and distance.distance <= $miles
Note: distance.endpoint and distance.startpoint fields must be indexed.