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.

What version of MySQL are you using? Have you considered using a trigger for this? — AJ.
– AJ., Commented Feb 1, 2010 at 14:07
MySql version 4, I am not that technical not aware of trigger. — John Jones
– John Jones, Commented Feb 1, 2010 at 14:08
Can you provide a more complete code example? Where do you use $companies and UPDATE unique_codes WHERE code = $code is doing nothing. — Felix Kling
– Felix Kling, Commented Feb 1, 2010 at 14:10
I have update the full code, I am writing all the updates to SQL file for import. I done this to speed it up so it doesnt do update to companies table. — John Jones
– John Jones, Commented Feb 1, 2010 at 14:17

acrosman · Accepted Answer · 2010-02-01 14:09:51Z

3

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.

answered Feb 1, 2010 at 14:09

acrosman

12.9k10 gold badges42 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

John Jones Over a year ago

Cheers for response, the trouble is the codes are random all begin with 9 and 8 numbers long, so I cant just do a simple auto increment.

John Jones Over a year ago

Hi, the correct PRIMARY key is add to companies = id and for unique_codes = code.

acrosman Over a year ago

Let me see if I understand at this point. You are trying to make sure that each code is assigned to at least 1 company, so you are trying to get 1 code out of the list of codes, and assign it to a company. Is that about right? Are the random codes you've generating already meaningful (i.e. could you re-generate them if you needed to)?

John Jones Over a year ago

The codes are meaningful, this is the problem each company must have a unique code, but inorder to make sure unqiue I have to UPDATE unique_codes table and SET it as used so it doesnt get used again.

acrosman Over a year ago

Get rid of your existing query4, and run it at the end with a large number of items it the where clause (1 big query instead of a million small ones should help). Better yet, rewrite to have it pull a list of used codes from the company table and for the where clause of the update. To get this to work I'd still suggest writting a SQL script to do the work directly instead of PHP; 1 million+ queries in 30 seconds is asking a lot.

Sarfraz · Accepted Answer · 2010-02-01 14:09:00Z

0

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"

answered Feb 1, 2010 at 14:09

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

Comments

rtacconi · Accepted Answer · 2010-02-01 14:26:23Z

0

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).

answered Feb 1, 2010 at 14:26

rtacconi

14.8k21 gold badges69 silver badges85 bronze badges

Collectives™ on Stack Overflow

Mysql PHP Script to slow needs refactor

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related