How to quickly write many records to the database in laravel while checking for uniqueness?

Question

Database design

Hello.

I am writing an application - a dictionary.
You need to write a lot of records to the database (up to 10 thousand).

I am importing new words into the database, now the words are written to the database one at a time, because before adding I check if the word is in the database.

Now it works very slowly.

Tell me how it can be accelerated? How not to write words one by one?

Answer 1

Found a solution

$myWords = Word::all();

        $wordsFromFile = [];

        foreach($words as $word => $translation)
        {
            $word = mb_strtolower($word);
            $translation = mb_strtolower($translation);

            $wordInDatabase = $myWords->where('word', $word)->first();

            if($wordInDatabase == null)
            {
                $wordsFromFile[] = [
                    'word' => $word,
                    'translation' => $translation,
                    'state' => 0,
                ];
            }
        }
        
        Word::insert($wordsFromFile);

Answer 2

You can create a large query and use either
INSERT IGNORE
ON DUPLICATE KEY UPDATE