0

I've got a common scenario here that I've yet to find out what the most effective way of going about this is.

Let's say I have a table of 100,000 users.

On the 5th of every month, I want to run a scheduler that will add an entry in the database for each user. This entry will be a message. So they essentially all receive a message on the 5th of every month.

At the moment, it could be done like this:

$user = \App\User::all();
foreach ($users as $user){
$message = new Message;
$message->user_id = $user->id;
$message->content = "Message goes here";
$message->save();
}

Essentially this creates 100k new entries in the database.

Of course this is just an example as it would be crazy to run a foreach on a 100,000 users.

I could perhaps utilise the insert method, but I can't see how that would work with large amounts of data.

What is the most efficient way of solving this issue? I can't imagine that queues would be efficient either because then it would essentially create 100,000 items in the queue resulting in the same, or even worse, performance.

slothinspace
  • 479
  • 1
  • 5
  • 17
  • 1
    Would it be possible to not insert messages at all, but maybe programmatically have messages being "send" to users whenever they login? Queued could work, let's say you run 1000 jobs inserting 100 records. It's slow, but it's that really a problem? It least the process won't fail on such case with small batches of records. – Maarten Veerman Oct 28 '20 at 15:55
  • Actually that is currently how I have it working right now! This was just a hypothetical scenario that I'm certain to come across in the very near-future. – slothinspace Oct 28 '20 at 15:56
  • 2
    I have a Laravel backend that needed to insert millions of records as fast as possible. In the end the trick was to follow the tips specified here https://mariadb.com/kb/en/how-to-quickly-insert-data-into-mariadb/ as much as possible. Using transactions is also a good thing, and performing a mass update with 1 call was they key. – Maarten Veerman Oct 28 '20 at 16:02
  • 1
    read this article tells about chunk and cursor https://blackdeerdev.com/laravel-chunk-vs-cursor/ – bhucho Oct 28 '20 at 16:04
  • Thanks all for the articles! – slothinspace Oct 28 '20 at 16:10
  • 1
    Would a raw `INSERT... SELECT...` statement do the job in 1 SQL statement? – Nigel Ren Oct 28 '20 at 16:25
  • https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile – nice_dev Oct 28 '20 at 16:45

0 Answers0