July 10th, 2000, 04:01 AM
Has anyone any experience of using MySQL to implement a web based address finder? I am looking to create one table from the UK Postcode (ZIP) Address File which has about 3 million street addresses and 1.7 million postcodes, index it on postcode, and then use php to allow a user to enter a postcode and retrieve the street address. I've seen a demo of this in action using CGI and I don't know what database/file structure at http://www.afd.co.uk/internet/postcode/index.htm but they charge a lot of money (£1500). Not that I'm a cheapskate but I can buy the data for about £850 so if it takes 2 days to develop something myself I save.
The questions are really -
How effecient is MySQL indexing given that it will have a high cardinality?
How quickly does a mass insert from a text file perform - 3 million rows of 'INSERT INTO....'?
Has anyone else done anything like this before?
July 12th, 2000, 11:08 PM
I don't know if I can be that much help, but here it goes:
There is a maximum limit on the size of a table at 5G (In case you are planning of exceeding that).
Also, I do believe that the speed of an INSERT statement depends on the speed of the server, and the speed of your connection (if you are transferring the data from your computer). I have done large INSERT statements before (none being 3 million rows), but I can tell you that running one with 10,000 to 20,000 rows takes about 2 minutes (with the data already on the server). So, at the same speed, it would take approx. 400 min. to do 3,000,000 rows (around 6 and a half hours). That is my best guess.