Mysql PHP Script to slow needs refactor

Question

Hi I have 2 mysql tables and they both need updating,

companies
unique_code

Companies holds 1 million records and I need a unique code asigning to each one, the problem I have is my PHP script grabs all the companies and in foreach loop grabs a unique code from table unique_code and updates, it also then updates unique_code table to flag the code has been used.

The PHP code just hangs for ages and reachs max execution limit. I am really stuck and needs these companies to have a unique code, can anyone think of another approach?

Stipped down code example.

 foreach ($aCompanies as $companies){

   $query="SELECT * FROM unique_code WHERE used = 0"

   foreach(unique_code as code){
       //  UPdate companies table
       $query = "UPDATE companies SET id = $code";
       // Flag code used
       $query = "UPDATE unique_codes WHERE code = $code";
   }
}

Cheers for your time.

Complete Code:

$query1 = "SELECT code FROM unique_codes WHERE used = 0\n";
$aUniqueCode = $oDbh->getAll($query1);

$query2 = "SELECT id FROM companies";
$aCompanies = $oDbh->getAll($query2);

foreach ($aCompanies as $companies){

    $query = "SELECT code FROM unique_codes WHERE used = '0' LIMIT 1";
    $oCode = $oDbh->getRow($query);

    $query3.= "UPDATE companies SET code = $oCode->code WHERE id = $companies->id\n";   

    $query4 = "UPDATE unique_codes SET used = '1' WHERE code = $oCode->code\n";
    $oDbh->query($query4);      
}
print print_r($query3).';';
exit;

What I am doing is not updating the companies I am exporting all the SQL's to a file so I can import at later date.

Answer 1

Have you turned on indexing on specific fields? Also make sure that you select required fields in your query rather than all (*):

$query="SELECT * FROM unique_code WHERE used = 0"

Instead:

$query="SELECT field1, field2, field3, etc FROM unique_code WHERE used = 0"

Answer 2

If you're just trying to add a new primary key to the table (and therefore a one time process) don't do it in PHP. Use MySQL directly and use alter table to get the job done. MySQL will be faster directly, and will not run into timing problems for long processes like PHP.

If you're trying to do something else, you might want to put up a bit more the table schema, it's not totally clear to me that I have your goal right.

Answer 3

I hope that the uniuque code is a primary key, plus check if the field used in your WHERE clause can be indexed, if it is not a primary key. T

How do you know that the database is the bottleneck? You can use Xdebug for profiling, so you will be able to see where is really the bottle neck.

You can use Memchace so you can store in memory the most used objects and reduce the database hits when reading (SELECT).