0

I'm currently working on wordle-like project and wanted to populate database with words.

Found a list of ~15k of words using regex. I've made an array [word(1), word(2), ..., word(n)] from which I looped through each of these words and added them into my database.

preg_match_all('/\w{5}/', $words, $matches);
foreach($matches[0] as $m) {
    $word = new Words();
    $word->setName($m);
    $entityManagerInterface->persist($word);
    $entityManagerInterface->flush();
        }

But it took over 30 sec and symfony stopped it because it thought it's infinite loop which is not.

I found the solution using set_time_limit(0); but after like 2 mins it stopped and I got an error about using too much memory.

I thought about splitting $words array into smaller and add them one by one.

So here's my question,

  • Is there any better way to add those words faster than this?

When it failed to add those words into my database I tried to delete all records using:

$query = $entityManagerInterface->createQuery('DELETE FROM App\Entity\Words e')
    ->execute();

It works fine but when I add words after execution of the last line, primary key doesn't start from "1" but it continues from last primary key.

Just curious,

  • Is it possible to make it start again from primary key "1".
Joypal
  • 178
  • 9
ch3ssnut
  • 47
  • 6
  • 2
    First question: You need to use [batch processing](https://www.doctrine-project.org/projects/doctrine-orm/en/2.13/reference/batch-processing.html#batch-processing), potentially over multiple requests, when using a very large dataset and the ORM. This is because you have a limited amount of time and memory in both PHP & the webserver platform to handle a single request. Second question is: that is how auto-increment functions in MySQL and other RDBMS platforms. You would need to [reset the AUTO_INCREMENT index](https://stackoverflow.com/q/1799020/1144627) after deleting the records. – Will B. Oct 27 '22 at 20:02
  • 1
    Regarding the resetting of the `AUTO_INCREMENT` index, it is a bad practice to do so outside of development. Instead the best-practice is to replace `AUTO_INCRMENT` in favor of [using UUID](https://stackoverflow.com/questions/40400401/php-and-doctrine-how-to-create-unique-ids), which enables persistent IDs without a need for a database. For more details see: [Best-practices for Doctrine ORM](https://ocramius.github.io/doctrine-best-practices/#/) written by a major contributor of the Doctrine repositories [Ocramius](https://github.com/ocramius). – Will B. Oct 27 '22 at 20:09
  • Lastly for populating a database you should utilize [Doctrine Migrations](https://symfony.com/bundles/DoctrineMigrationsBundle/current/index.html), [Doctrine Fixtures](https://symfony.com/bundles/DoctrineFixturesBundle/current/index.html) or similar types of libraries. While Doctrine Migrations is not supposed to be used with the ORM, Instead of manually executing queries with various scripts, it will help to with debugging and maintaining the database schema and data updates throughout your project's lifespan. – Will B. Oct 27 '22 at 20:28
  • 1
    Look up `LOAD DATA INFILE` syntax - it's the only way to insert thousands of records quickly. – Robo Robok Oct 28 '22 at 08:52
  • @ch3ssnut Do not `->flush()` in a loop. Move it out right after. – V-Light Oct 29 '22 at 09:37

0 Answers0