Kaizokupuffball Kaizokupuffball - 7 months ago 28
PHP Question

Laravel from database table to another table 1mill rows, slow

So basically, I got 2 tables.
The first table contains 1 million rows or something, with an empty field called 'telefon'.

Now, I got a second table, which has the field values for 'telefon' in the other table.

I came up with this solution, but this takes forever. It has been an hour, and when inspecting the database table, only 1600 rows are done. Is there any faster ways of doing this? Thanks in advance.

DB::table('phones') -> orderBy('id') -> chunk(100, function($old) {
foreach ($old as $x) {
DB::table('companies')
-> where('organisasjonsnummer', $x -> businessCode)
-> update([
'telefon' => $x -> contact
]);
}
});

Answer

Huh, foreach + queries is almost always bad. If I am not mistaken, you would like to do this:

UPDATE companies, phones SET companies.telefon = phones.contact WHERE companies.organisasjonsnummer = phones.businessCode

It may be very slow if there's no index on companies.organisasjonsnummer and phones.businessCode columns, but it can take a lot of time to index them now as well, so I'm not sure if there's any benefit to index them now if they won't be used later. Anyway, using a single query should be faster at least to some extent.

Comments