quick way of performing this mysql

heres my code:

function toAscii($str, $replace=array(), $delimiter='-') {
    if( !empty($replace) ) {
        $str = str_replace((array)$replace, ' ', $str);
    }

    $clean = iconv('UTF-8', 'ASCII//TRANSLIT', $str);
    $clean = preg_replace("/[^a-zA-Z0-9\/_|+ -]/", '', $clean);
    $clean = strtolower(trim($clean, '-'));
    $clean = preg_replace("/[\/_|+ -]+/", $delimiter, $clean);

    return $clean;
}

$sql = mysql_query("select * from cities_dev order by PostalCode asc") or die(mysql_error());

// id, id_province, id_city, postal_code, latitude, longitude

while ($row = mysql_fetch_assoc($sql))
{
    $slug = toAscii($row['City']);
    $id_province = mysql_result(mysql_query("select id from provinces where abbr='".mysql_real_escape_string($row['ProvinceCode'])."'"), 0) or die($row['City'].' - prov');
    $id_city = mysql_result(mysql_query("select id from cities where slug='".$slug."' and id_province='".$id_province."'"), 0) or die($id_province . ' - ' . $row['City'] .' - ' . $row['PostalCode'] . ' - city');
    $postal_code = mysql_real_escape_string($row['PostalCode']);
    $latitude = mysql_real_escape_string($row['Latitude']);
    $longitude = mysql_real_escape_string($row['Longitude']);

    mysql_query("insert into geo (`id_province`, `id_city`, `postal_code`, `latitude`, `longitude`) 
                 values ('$id_province', '$id_city', '$postal_code', '$latitude', '$longitude')") or die('insert');
}

what i am trying to do is insert a new row for each postalcode/lat/lng row. there are about 750,000 rows. i need to grab the province ID and city ID from 2 other tables for eac开发者_JAVA技巧h new row inserted.

this sql works, but is taking FOREVER. can anyone show me a better way of coding this?

---

I don't think any of the other suggestions will really help you speed this up..

You can change this entire thing into a INSERT ... SELECT query. There's plenty of documentation on how to do this. The select itself will be slow if there's a lot of postal codes, but if you are using InnoDB, have good a good amount for the page buffer size (global setting) so all the data will fit in memory, this should help quite a bit.

MyISAM can also be very slow for inserts, so I would also recommend changing the second table to InnoDB if you haven't already.

---

You should consider doing a batch insert to insert multiple rows per query. This can provide a huge performance increase, but be aware that there is a limit on the size of a single query. I believe the limit is based on the max_allowed_packet variable in MySQL - on one of my servers for example, this is set to 1MB.

I would suggest starting with a batch of around 100 rows per insert to see the improvement and adjust from there if you need to based on the size of your query and the max allowed packet size.

---

Well, the actual way to do this properly now a days would be using MySQLi prepared statements. This extension provides support for all of the old functions as well as a plenty of improvements to the code.

You could read more on prepared statements here or here.

---

Try a Stored Procedure (http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx -- This was the best I could find on short notice). It lets you execute your code on the server all at once (giving it some initial inputs) instead of talking to the server between each action. It should greatly decrease your execute time.