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

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

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

Post 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
User avatar
dbevfat
Forum Contributor
Posts: 126
Joined: Tue Jun 28, 2005 2:47 pm
Location: Ljubljana, Slovenia

Post by dbevfat »

I think 5 mio records shouldn't slow down the system too much, provided the table is properly indexed.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post 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.
Post Reply