1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Rep Power
    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?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Rep Power
    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.

    Good Luck!
    datera@datera.com http://www.datera.com

Similar Threads

  1. Controlling Multiple table Heights in a Browser
    By AttaBoy in forum HTML Programming
    Replies: 1
    Last Post: December 10th, 2003, 04:24 AM
  2. How do I Generate Access 200 Db from script?
    By johnnyr77 in forum Database Management
    Replies: 0
    Last Post: September 1st, 2003, 05:45 PM
  3. Soccer League table
    By silverduck in forum Database Management
    Replies: 3
    Last Post: August 19th, 2003, 04:46 AM
  4. help on 3 table join query?
    By smakz in forum Database Management
    Replies: 1
    Last Post: November 19th, 2002, 04:00 AM
  5. Performance Issue - large table
    By ggnanaraj in forum PostgreSQL Help
    Replies: 4
    Last Post: November 17th, 2002, 11:28 PM

IMN logo majestic logo threadwatch logo seochat tools logo