I have a pretty large database which was imported using the csv 'LOAD DATA INFILE' method. Some cells though need to be manipulated and unfortunately i cant do that during the import. I have to do it afterwards since PHP is much easier to play with, not to mention that there are other table cells that need to be changed which is impossible to do with mysql commands. One example of these type of manipulations is the 'Telephone' cell. I have to break the telephone number then update separately 2 different cells of the same row. For example:
$myQ = my_db_query("SELECT companyId, Telephone FROM companies WHERE 1");
while ($row = my_db_fetch_array($myQ))
{
if($row["Telephone"] != ""){
$Telefon_area = "";
$Telefon_num = "";
$pieces = explode(" ", $row["Telephone"]);
$Telefon_area = trim( $pieces[0] );
$Telefon_num = trim( $pieces[1] );
my_db_query("UPDATE companies SET `Telefon_area` = '$Telefon_area', `Telefon_num` = '$Telefon_num' WHERE `companyId` = ".$row["companyId"]);
}
}
The problem i am facing with this approach is that (although i've indexed column Telephone), it is veeeery slow, because i am updating 1.5mil entries.
Is there a faster way of doing this ?
