Level 75
Am I missing something, how are you importing (adding the record). Try an export to csv then import perhaps.
Jun 14, 2024
8
Level 13
Help me speed up this import
I wish to import the user_id from my old laravel database. There are about 240,000 records and the following code is running all night; and still not finished importing. I know it's not optimized, but it should have finished within an hour or so max (IMHO). How should I go about it?
public function handle()
{
Post::where('community_id', '=', 1)->whereNotNull('parent_id')->where('type', '=', 'threads')->chunk(500, function ($replies) {
foreach($replies as $reply) {
$old_reply = DB::connection('10_july_2021')->table('discussions_replies')->where('created_at', '=', $reply->created_at)->first();
$reply->timestamps = false;
$reply->user_id = $old_reply->user_id;
$reply->save();
$reply->timestamps = true;
}
$this->info("Updated: " . $this->count += $replies->count()) . " user_ids.";
});
}
Level 13
@jlrdw - Thank you for your reply. Here's a quick overview:
- I have the destination "posts" table where I need to import the 'user_id' from old database "old_db".
- Because my posts database does not follow the "Ids", I have to rely on unique 'created_at' entries from old_db to find the relevant 'user_ids'.
That's why I'm importing records based on 'WHERE' clause.
I'm not sure how'd the exporting CSV and then importing it will work. How will it know which user_id to update based on 'created_at'?
Level 75
@thebigk if you already have id's won't that mess up foreign keys?
You would have to change each foreign key to match a new id??
Level 13
@jlrdw - I don't have the ids. The id on my destination posts table has different ids for the imported posts and I can't change it now. The only way, as it seems, is to update the user_id based on created_at timestamp from the source table.
Level 122
because you are doing 240,000 individual database queries.
Did you ensure that created_at on the historic data has an index?
What happens if you find more than one record with the same created_at
Also reduce the Post query to just the values you need
1 like
Level 13
@Snapey Yeah, and I have no clue what's the better way to do that. I'm exploring upsert currently.
-
Let me try adding index on the
created_at. It's a laravel created table and I thought it's automatically indexed. I never inspected. -
There aren't any duplicates in the 'created_at`. This was from a forum that wasn't very busy to have multiple records inserted at the same time.
-
Attempting Post query without the community_id part.
Will report back in some time.
Level 74
Why on earth are you doing this programmatically with php?
You should let the database do the lifting, and since it seems to use the same database but different shemas then you can just do.
INSERT INTO newdb.posts VALUES (SELECT * FROM oldbd.posts);
1 like
Level 13
Update: I was able to fix the speed by simply indexing the 'created_at' on my source table. What took about 9 hours before was completed in just about 3 minutes. Indexing is super powerful!
Please or to participate in this conversation.