orm:schema-tool:update Execution plan not optimal when replacing primary key (mysql) #6747

New Issue

admin · 2026-01-22T15:37:58+01:00

admin commented

2026-01-22 15:37:58 +01:00

Originally created by @andig on GitHub (Jun 8, 2021).

I'm converting the following entity from using dedicated PK to composite PK by removing the id column. From:

/**
* @Entity
* @Table(
* 	name="data",
*	uniqueConstraints={@UniqueConstraint(name="data_unique", columns={"channel_id", "timestamp"})}
* )
*/
class Data
{
    /**
    * @Id
    * @Column(type="integer", nullable=false)
    * @GeneratedValue(strategy="IDENTITY")
    */
    protected $id;

    /**
    * @Column(type="bigint", nullable=false)
    */
    protected $timestamp;

    /**
    * @Column(type="float", nullable=false)
    */
    protected $value;

    /**
    * @ManyToOne(targetEntity="Channel", inversedBy="data")
    * @JoinColumn(name="channel_id", referencedColumnName="id", nullable=false)
    */
    protected $channel;
}

To:

/**
* @Entity
* @Table(name="data")
*/
class Data
{
    /**
    * @Id
    * @ManyToOne(targetEntity="Channel", inversedBy="data")
    * @JoinColumn(name="channel_id", referencedColumnName="id")
    */
    protected $channel;

    /**
    * @Id
    * @Column(type="bigint")
    */
    protected $timestamp;

    /**
    * @Column(type="float", nullable=false)
    */
    protected $value;
}

This creates the following execution plan:

ALTER TABLE data MODIFY id INT NOT NULL;
DROP INDEX data_unique ON data;
ALTER TABLE data DROP PRIMARY KEY;
ALTER TABLE data DROP id, CHANGE channel_id channel_id INT NOT NULL;
ALTER TABLE data ADD PRIMARY KEY (channel_id, timestamp);

This plan seems not optimal as it needs to lock and copy the entire data table in steps 1,3,4.
My assumption is that steps one and 3 could be combined into one step and potentially running step 2 after step 5 might be a nop.

Originally created by @andig on GitHub (Jun 8, 2021). I'm converting the following entity from using dedicated PK to composite PK by removing the id column. From: /** * @Entity * @Table( * name="data", * uniqueConstraints={@UniqueConstraint(name="data_unique", columns={"channel_id", "timestamp"})} * ) */ class Data { /** * @Id * @Column(type="integer", nullable=false) * @GeneratedValue(strategy="IDENTITY") */ protected $id; /** * @Column(type="bigint", nullable=false) */ protected $timestamp; /** * @Column(type="float", nullable=false) */ protected $value; /** * @ManyToOne(targetEntity="Channel", inversedBy="data") * @JoinColumn(name="channel_id", referencedColumnName="id", nullable=false) */ protected $channel; } To: /** * @Entity * @Table(name="data") */ class Data { /** * @Id * @ManyToOne(targetEntity="Channel", inversedBy="data") * @JoinColumn(name="channel_id", referencedColumnName="id") */ protected $channel; /** * @Id * @Column(type="bigint") */ protected $timestamp; /** * @Column(type="float", nullable=false) */ protected $value; } This creates the following execution plan: ALTER TABLE data MODIFY id INT NOT NULL; DROP INDEX data_unique ON data; ALTER TABLE data DROP PRIMARY KEY; ALTER TABLE data DROP id, CHANGE channel_id channel_id INT NOT NULL; ALTER TABLE data ADD PRIMARY KEY (channel_id, timestamp); This plan seems not optimal as it needs to lock and copy the entire `data` table in steps 1,3,4. My assumption is that steps one and 3 could be combined into one step and potentially running step 2 after step 5 might be a nop.

admin referenced this issue

2026-01-22 16:06:30 +01:00

[PR #6747] Add Doctrine\ORM\Query\Expr::mod() #10125

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#6747