Redundant index created when FK column part of composite index #5744

New Issue

admin · 2026-01-22T15:16:19+01:00

admin commented

2026-01-22 15:16:19 +01:00

Originally created by @stevepetcu on GitHub (Oct 19, 2017).

Hey,

Besides a post on Google Forms from a few years ago, I haven't found anything on this.

Say you create a composite index, like this, on a table:

indexes={
    @ORM\Index(name="idx_country_code_postal_code", columns={"country_code", "postal_code"})
}

, where country_code happens to also be a FK.

I'm working with MySQL, by the way. From what I understand, this index should satisfy its FK indexing requirements (the country_code is the first field in the composite index). However, Doctrine generates the following query:

[stuff...]
    INDEX idx_d4e6f81f026bb7c (country_code), 
    INDEX idx_country_code_postal_code (country_code, postal_code)
[...the rest of stuff]

Is this expected behaviour and I'm missing something, or is this something that can be improved?

Originally created by @stevepetcu on GitHub (Oct 19, 2017). Hey, Besides a post on Google Forms from a few years ago, I haven't found anything on this. Say you create a composite index, like this, on a table: ``` indexes={ @ORM\Index(name="idx_country_code_postal_code", columns={"country_code", "postal_code"}) } ``` , where `country_code` happens to also be a FK. I'm working with MySQL, by the way. From what I understand, this index should satisfy its FK indexing requirements (the `country_code` is the first field in the composite index). However, Doctrine generates the following query: ``` [stuff...] INDEX idx_d4e6f81f026bb7c (country_code), INDEX idx_country_code_postal_code (country_code, postal_code) [...the rest of stuff] ``` Is this expected behaviour and I'm missing something, or is this something that can be improved?

admin commented

2026-01-22 15:16:20 +01:00

@Ocramius commented on GitHub (Oct 19, 2017):

I think this was fixed for single column identifiers, but not for multi-column ones. Can you try and see if the issue persists on master?

@Ocramius commented on GitHub (Oct 19, 2017): I think this was fixed for single column identifiers, but not for multi-column ones. Can you try and see if the issue persists on `master`?

admin commented

2026-01-22 15:16:20 +01:00

@stevepetcu commented on GitHub (Oct 19, 2017):

I'm getting an identical result with the dev-master version for my schema creation SQL.

And yes, when it comes to single-column indexes, I can specify them myself and Doctrine will only generate those ones (useful for having some nice index names). :)

@stevepetcu commented on GitHub (Oct 19, 2017): I'm getting an identical result with the `dev-master` version for my schema creation SQL. And yes, when it comes to single-column indexes, I can specify them myself and Doctrine will only generate those ones (useful for having some nice index names). :)

admin commented

2026-01-22 15:16:21 +01:00

@mateuszsip commented on GitHub (Oct 23, 2017):

Looks like this is cause of your problems.

A simple test case:

    public function testFulfilledWithCompositeIndexCoveringSingleColumnIndex()
    {
        $implicitFkIndex = new Index('fk_foo', array('col1'), false, false, array(), array());
        $explictIndex = new Index('bar', array('col1', 'col2'), false, false, array(), array());

        self::assertTrue($implicitFkIndex->isFullfilledBy($explictIndex));
        self::assertFalse($explictIndex->isFullfilledBy($implicitFkIndex));
    }

confirms that it doesn't work.

I can try to do something about it, but could need some help.

@mateuszsip commented on GitHub (Oct 23, 2017): Looks like [this](https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Index.php#L203) is cause of your problems. A simple test case: ``` public function testFulfilledWithCompositeIndexCoveringSingleColumnIndex() { $implicitFkIndex = new Index('fk_foo', array('col1'), false, false, array(), array()); $explictIndex = new Index('bar', array('col1', 'col2'), false, false, array(), array()); self::assertTrue($implicitFkIndex->isFullfilledBy($explictIndex)); self::assertFalse($explictIndex->isFullfilledBy($implicitFkIndex)); } ``` confirms that it doesn't work. I can try to do something about it, but could need some help.

admin commented

2026-01-22 15:16:21 +01:00

@stevepetcu commented on GitHub (Oct 23, 2017):

There is a typo in the isFullfilledBy method name and it's a public method written in 2010. Nooooo!

@kejwmen nice, I'm taking a look at it, see if I figure out how it works. 👍

@stevepetcu commented on GitHub (Oct 23, 2017): There is a typo in the `isFullfilledBy` method name and it's a public method written in 2010. Nooooo! @kejwmen nice, I'm taking a look at it, see if I figure out how it works. 👍

admin commented

2026-01-22 15:16:21 +01:00

@stevepetcu commented on GitHub (Oct 23, 2017):

I made a PR with a fix for this: https://github.com/doctrine/dbal/pull/2895

I'm not sure whether I'm supposed to make it against the develop, or the master, but I had a bunch of failing tests on the develop, so I made it against the master. :)

@Ocramius I also don't know is this is the place to ask for a code review, but please let me know if I should change anything.

@stevepetcu commented on GitHub (Oct 23, 2017): I made a PR with a fix for this: https://github.com/doctrine/dbal/pull/2895 I'm not sure whether I'm supposed to make it against the `develop`, or the `master`, but I had a bunch of failing tests on the `develop`, so I made it against the master. :) @Ocramius I also don't know is this is the place to ask for a code review, but please let me know if I should change anything.

admin commented

2026-01-22 15:16:21 +01:00

@Ocramius commented on GitHub (Oct 23, 2017):

Seen the patch, won't get to do proper githubbing for a while though, as
I'm always at the computer only for "paid work" these days 🤐

On 23 Oct 2017 13:47, "Stefan" notifications@github.com wrote:

I made a PR with a fix for this: doctrine/dbal#2895
https://github.com/doctrine/dbal/pull/2895

I'm not sure whether I'm supposed to make it against the develop, or the
master, but I had a bunch of failing tests on the develop, so I made it
against the master. :)

@Ocramius https://github.com/ocramius I also don't know is this is the
place to ask for a code review, but please let me know if I should change
anything.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/6783#issuecomment-338633393,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakBCA6YtvIK3iIyRkHulqUJPDfhwMks5svHzHgaJpZM4P_ZF6
.

@Ocramius commented on GitHub (Oct 23, 2017): Seen the patch, won't get to do proper githubbing for a while though, as I'm always at the computer only for "paid work" these days 🤐 On 23 Oct 2017 13:47, "Stefan" <notifications@github.com> wrote: > I made a PR with a fix for this: doctrine/dbal#2895 > <https://github.com/doctrine/dbal/pull/2895> > > I'm not sure whether I'm supposed to make it against the develop, or the > master, but I had a bunch of failing tests on the develop, so I made it > against the master. :) > > @Ocramius <https://github.com/ocramius> I also don't know is this is the > place to ask for a code review, but please let me know if I should change > anything. > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <https://github.com/doctrine/doctrine2/issues/6783#issuecomment-338633393>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJakBCA6YtvIK3iIyRkHulqUJPDfhwMks5svHzHgaJpZM4P_ZF6> > . >

admin commented

2026-01-22 15:16:22 +01:00

@stevepetcu commented on GitHub (Oct 23, 2017):

Heh, I know all about that. I'm in-between jobs at the moment, so I have some free time.

I saw there were some failed tests. I only ran the ones under the DBAL/Schema locally, as there was a large number of skipped tests and some that were failing due to missing drivers on the whole, but I'll have to check what's failing more carefully, probably tomorrow. I think there are some fails due to missing drivers on the CI, too, is that normal?

@stevepetcu commented on GitHub (Oct 23, 2017): Heh, I know all about that. I'm in-between jobs at the moment, so I have some free time. I saw there were some failed tests. I only ran the ones under the `DBAL/Schema` locally, as there was a large number of skipped tests and some that were failing due to missing drivers on the whole, but I'll have to check what's failing more carefully, probably tomorrow. I think there are some fails due to missing drivers on the CI, too, is that normal?

admin commented

2026-01-22 15:16:23 +01:00

@stevepetcu commented on GitHub (Oct 23, 2017):

By the way, I ran a schema update sql dump on my app. It will remove 4 redundant indexes. That's pretty significant, as I only have 9 entities at the moment.

I also ran a schema create and it looks OK.

@stevepetcu commented on GitHub (Oct 23, 2017): By the way, I ran a schema update sql dump on my app. It will remove 4 redundant indexes. That's pretty significant, as I only have 9 entities at the moment. I also ran a schema create and it looks OK.

admin referenced this issue

2026-01-22 16:13:51 +01:00

[PR #10455] Make Annotations/Attribute mapping drivers report fields for the classes where they are declared #12372

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