Feature request: avoid duplicating index for foreign key column if already covered as primary column by other indexes #6678

New Issue

admin · 2026-01-22T15:36:53+01:00

admin commented

2026-01-22 15:36:53 +01:00

Originally created by @andig on GitHub (Apr 4, 2021).

I have the following simple data table:

/**
 * Data entity
 *
 * @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;

	/**
	 * Ending timestamp of period in ms since 1970
	 *
	 * @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;

which gives

CREATE TABLE `data` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  `value` float NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `data_unique` (`channel_id`,`timestamp`),
  KEY `IDX_ADF3F36372F5A1AA` (`channel_id`)
)

Since data_unique already covers channel_id in the first position, the additional IDX_ADF3F36372F5A1AA key on channel_id should not be necessary and the on-disk space for that index could be saved.

Originally created by @andig on GitHub (Apr 4, 2021). I have the following simple `data` table: ``` /** * Data entity * * @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; /** * Ending timestamp of period in ms since 1970 * * @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; ``` which gives ``` CREATE TABLE `data` ( `id` int(6) NOT NULL AUTO_INCREMENT, `channel_id` int(11) DEFAULT NULL, `timestamp` bigint(20) NOT NULL, `value` float NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `data_unique` (`channel_id`,`timestamp`), KEY `IDX_ADF3F36372F5A1AA` (`channel_id`) ) ``` Since `data_unique` already covers `channel_id` in the first position, the additional `IDX_ADF3F36372F5A1AA` key on `channel_id` should not be necessary and the on-disk space for that index could be saved.

admin closed this issue

2026-01-22 15:36:54 +01:00

admin commented

2026-01-22 15:36:54 +01:00

@andig commented on GitHub (Apr 7, 2021):

@beberlei does the feature request make sense for you? The example above might be a bit too complicated. The same reasoning would apply when composite primary keys are used and already cover the foreign key column.

@andig commented on GitHub (Apr 7, 2021): @beberlei does the feature request make sense for you? The example above might be a bit too complicated. The same reasoning would apply when composite primary keys are used and already cover the foreign key column.

admin commented

2026-01-22 15:36:54 +01:00

@beberlei commented on GitHub (Apr 10, 2021):

I am afraid we will introduce ourselves to a lot of edge cases should we make this a feature, while this index approach here may be inefficient space-wise, but access wise its good. If you want a more optimized schema, then I would suggest to address this with doctrine migrations or a schema visitor of your own.

@beberlei commented on GitHub (Apr 10, 2021): I am afraid we will introduce ourselves to a lot of edge cases should we make this a feature, while this index approach here may be inefficient space-wise, but access wise its good. If you want a more optimized schema, then I would suggest to address this with doctrine migrations or a schema visitor of your own.

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