DDC-1602: Executors for Class Table Inheritance (JOINED) are extremely slow on MySQL #2012

New Issue

admin · 2026-01-22T13:37:35+01:00

admin commented

2026-01-22 13:37:35 +01:00

Originally created by @doctrinebot on GitHub (Jan 15, 2012).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user majkl578:

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:

/****
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"root" = "Root", "a" = "SubA"})
 */
class Root
{
    /****
     * @Column(type="integer")
     * @Id
     * @GeneratedValue
     */
    private $id;

    /****
     * @Column(type="integer")
     */
    private $xyz;
}

/****
 * @Entity
 */
class SubA extends Root
{
    /****
     * @Column(type="integer")
     */
    private $foo;
}

Now lets perform a simple DQL UPDATE:

UPDATE Entities\Root r SET r.xyz = 123 WHERE r.id > ?

(note: always the upper half of entries)
Which creates following SQLs:

CREATE TEMPORARY TABLE Root*id*tmp (id INT NOT NULL)
INSERT INTO Root*id_tmp (id) SELECT t0.id FROM Root t0 LEFT JOIN SubA s0_ ON t0.id = s0*.id WHERE t0.id > 25000
UPDATE Root SET xyz = 123 WHERE (id) IN (SELECT id FROM Root*id*tmp)
DROP TEMPORARY TABLE Root*id*tmp

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 ||
| MySQL | 0.26s | 0.35s | 1.1s | 3.68s | 14.13s | 54.44s | 338s |
| PostgreSQL | 0.10s | 0.10s | 0.13s | 0.15s | 0.22s | 0.35s | 1.01s |

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.

Originally created by @doctrinebot on GitHub (Jan 15, 2012). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user majkl578: 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: ``` /**** * @Entity * @InheritanceType("JOINED") * @DiscriminatorColumn(name="discr", type="string") * @DiscriminatorMap({"root" = "Root", "a" = "SubA"}) */ class Root { /**** * @Column(type="integer") * @Id * @GeneratedValue */ private $id; /**** * @Column(type="integer") */ private $xyz; } ``` ``` /**** * @Entity */ class SubA extends Root { /**** * @Column(type="integer") */ private $foo; } ``` Now lets perform a simple DQL UPDATE: ``` UPDATE Entities\Root r SET r.xyz = 123 WHERE r.id > ? ``` (note: always the upper half of entries) Which creates following SQLs: ``` CREATE TEMPORARY TABLE Root*id*tmp (id INT NOT NULL) INSERT INTO Root*id_tmp (id) SELECT t0.id FROM Root t0 LEFT JOIN SubA s0_ ON t0.id = s0*.id WHERE t0.id > 25000 UPDATE Root SET xyz = 123 WHERE (id) IN (SELECT id FROM Root*id*tmp) DROP TEMPORARY TABLE Root*id*tmp ``` 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 || | **MySQL** | 0.26s | 0.35s | 1.1s | 3.68s | 14.13s | 54.44s | 338s | | **PostgreSQL** | 0.10s | 0.10s | 0.13s | 0.15s | 0.22s | 0.35s | 1.01s | 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](https://github.com/Majkl578/doctrine2-slow-executors).

admin added the Improvement label 2026-01-22 13:37:35 +01:00

admin commented

2026-01-22 13:37:36 +01:00

@doctrinebot commented on GitHub (Jan 15, 2012):

Comment created by @beberlei:

Its not a bug as it works. The performance drawback of JTI is discussed in the manual http://www.doctrine-project.org/docs/orm/2.1/en/reference/inheritance-mapping.html.

Changing this would be an improvement where we would hint if databases prefer subselects or joins for different operations. This would increase complexity of the SQL generation since now we are getting along with just one SQL generation strategy.

@doctrinebot commented on GitHub (Jan 15, 2012): Comment created by @beberlei: Its not a bug as it works. The performance drawback of JTI is discussed in the manual http://www.doctrine-project.org/docs/orm/2.1/en/reference/inheritance-mapping.html. Changing this would be an improvement where we would hint if databases prefer subselects or joins for different operations. This would increase complexity of the SQL generation since now we are getting along with just one SQL generation strategy.

admin commented

2026-01-22 13:37:37 +01:00

@doctrinebot commented on GitHub (May 11, 2012):

Comment created by majkl578:

Any chance to get this implemented before 2.3?

@doctrinebot commented on GitHub (May 11, 2012): Comment created by majkl578: Any chance to get this implemented before 2.3?

admin commented

2026-01-22 13:37:37 +01:00

@doctrinebot commented on GitHub (May 11, 2012):

Comment created by majkl578:

I've made a change in DBAL and ORM code to implement a solution issue. It's currently more likely a proof of concept.

With the change, my results are (approximately):
|| no. of entries || 500 || 1000 || 2500 || 5000 || 10000 || 20000 || 50000 ||
| MySQL | 0.17s | 0.19s | 0.21s | 0.26s | 0.27s | 0.37s | 0.92s |

Currently only update executor was changed.
DBAL branch with changes: https://github.com/Majkl578/doctrine-dbal/tree/DDC-1602
ORM branch with changes: https://github.com/Majkl578/doctrine2/tree/DDC-1602

Looking forward for your opinions.

@doctrinebot commented on GitHub (May 11, 2012): Comment created by majkl578: I've made a change in DBAL and ORM code to implement a solution issue. It's currently more likely a proof of concept. With the change, my results are (approximately): || no. of entries || 500 || 1000 || 2500 || 5000 || 10000 || 20000 || 50000 || | **MySQL** | 0.17s | 0.19s | 0.21s | 0.26s | 0.27s | 0.37s | 0.92s | Currently only update executor was changed. DBAL branch with changes: https://github.com/Majkl578/doctrine-dbal/tree/[DDC-1602](http://www.doctrine-project.org/jira/browse/DDC-1602) ORM branch with changes: https://github.com/Majkl578/doctrine2/tree/[DDC-1602](http://www.doctrine-project.org/jira/browse/DDC-1602) Looking forward for your opinions.

admin commented

2026-01-22 13:37:37 +01:00

@doctrinebot commented on GitHub (Jun 27, 2012):

Comment created by majkl578:

bump

@doctrinebot commented on GitHub (Jun 27, 2012): Comment created by majkl578: bump

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2012