Affects Version/s: 2.2-BETA2
Fix Version/s: None
Security Level: All
Environment:Debian, MySQL 5.5.17
Update and delete executors for Class Table Inheritance (JOINED) are extremely slow on MySQL platform. It is most probably due to use of subselect on the temporary table.
The slowdown is really significant as the table size increases. As an example, lets have a root entity with one subclass:
Now lets perform a simple DQL UPDATE:
(note: always the upper half of entries)
Which creates following SQLs:
The time spent on this on MySQL 5.5.17 and PostgreSQL 9.1 is:
|no. of entries||500||1000||2500||5000||10000||20000||50000|
As you can see, MySQL is drastically slower on even relatively small tables. This currently makes Doctrine unusable for this type of inheritance on MySQL. The solution probably would be to avoid subselect in WHERE clause in Doctrine\ORM\Query\Exec\MultiTableUpdateExecutor and Doctrine\ORM\Query\Exec\MultiTableDeleteExecutor.
Feel free to try/modify the test script yourself, it's here.