Question about db_index=False and composite indices

Using Django Using the ORM
1

Hello hello,

I have a model similar to this:

class event(models.Model):
    label = models.CharField(...)
    type = models.PositiveSmallIntegerField('Type', ...)
    
    model1 = models.ForeignKey('models.Model1', null=True, blank=True, on_delete=models.CASCADE)
    model2 = models.ForeignKey('models.Model2', null=True, blank=True, on_delete=models.CASCADE)

Now when migrating, django automatically creates:

  • database constraints
  • two indices, using model1_id and model2_id as the columns.

And that’s fine.

However, let’s imagine I want to index things slightly more cleverly, for example:

class event(models.Model):
    label = models.CharField(...)
    type = models.PositiveSmallIntegerField('Type', ...)
    
    model1 = models.ForeignKey('models.Model1', null=True, blank=True, on_delete=models.CASCADE)
    model2 = models.ForeignKey('models.Model2', null=True, blank=True, on_delete=models.CASCADE)

    class Meta:
        indexes = [
            models.Index(
                name='my_composite_index_name',
                fields=['model1', 'type']
            ),
            models.Index(
                name='my_composite_index_name_2',
                fields=['model2', 'type']
            ),
        ]

or even:

    class Meta:
        indexes = [
            models.Index(
                name='my_composite_index_name',
                fields=['model1', 'model2', 'type']
            ),
        ]

That looks like it is going to work, until we run an explain against the SQL (mysql 8.xx, django 3.2.14) generated for a query similar to the following:

qs = Model1.objects.annotate(
    event_count=SubqueryCount(
        Event.objects.only('pk').filter(
            model1__pk=OuterRef('pk'),
            type=1,
        )
    )
).filter(
     event_count__gt=0,
)

The query plan seems to indicate that the non composite index is always picked. The one created by django.

So then let’s choose and turn off indexing for that foreign key:

model1 = models.ForeignKey('models.Model1', null=True, blank=True, on_delete=models.CASCADE, db_index=False)
  • run makemigrations
  • run migrate…

and message is: No migration to apply.

What am I doing wrong?

2

For whatever reason then - generally due to the statistics of the distribution of the data - MySQL has determined that using the single-column index is going to be more performant than using the composite index. (And if the table / distribution is small enough, there would be cases where it could even decide that a table scan is the best way to do it.)

Make sure your statistics are up to date and rely upon the database query planner doing the right thing. It’s a no-win situation trying to out-guess it.

That’s an interesting situation. This might be one of those cases where migrations isn’t going to delete something that already exists.

If I were curious enough about it, I’d copy the project to a different location, delete the existing migrations, and rerun makemigrations to see what gets built.

Then, if it’s really important for you to get rid of that index, you could always create a migration manually.

3

This might be one of those cases where migrations isn’t going to delete something that already exists.

Argh. :slight_smile:

Actually my example above is not 100% right/accurate. See below.

The following:

    class Meta:
        indexes = [
            models.Index(
                name='my_composite_index_name',
                fields=['model1', 'type']
            ),
        ]

does indeed cause the composite index to be used.

However this:

    class Meta:
        indexes = [
            models.Index(
                name='my_composite_index_name',
                fields=['type', 'model1']
            ),
        ]

does not.

So order of the fields matters.

In the fields=['model1', 'type'] case, the first position held by the model1 FK causes the plan to use that composite index indeed.
Conversely, fields=['type', 'model1'] is ignored in favour of the single model1 index.

Still remains the issue of “why Oh Why is db_index-=False” not causing the removal of the index in question… ? Would be great to have certainty on this one. The main reason being that composite indices can make single indices redundant, and so not needed. Anyone from the django team able to shed some insight?

Thanks!

4

Yup yup. To dive into this more, it becomes a database question. I’d suspect this is because the model1 column is needed to evaluate the join and then the type is only part of the where clause. The join wants to use an index across the whole table rather than one that’s qualified by the type first. Hopefully that makes sense.

That should generate a new migration as far as I know. Have you checked the previous migration for this field to see if specifies db_index=False?

5

Yep:

    migrations.AlterField(
            model_name='event',
            name='model1',
            field=models.ForeignKey(blank=True, ***db_index=False***, null=True, on_delete=django.db.models.deletion.CASCADE, to='models.Model1'),
        ),

It’s there, but not applied. :[

6

Use manage.py showmigrations -v 2 to see what migrations have been run. I’m assuming that migration has already been run if it’s not being applied. I specified -v 2 so you can see when that migration was run.

7 
 [X] 0034_auto_20220809_1127 (applied at 2022-08-09 10:28:22)

so it has been applied. But the single index is still present.

8

Were you editing that migration in place? Or has it remained the same since its creation?

9

it has remained the same apart from the date/timestamp bits - as I have dumped the original table (as a restore point after playing on local) / mademigrations / migrated / removed the relevant row in django_migrations to run tests. Multiple times.

The end result is: after running this latest file (and its equivalent test siblings), the single index for model1 remains in the database.

So unless there is smth very specific in the setup of this model (which may very well be the case, tbh), db_index=False does not remove the index.
Or could it be trying … and run into an exception that is silenced…?

10

I don’t have a mysql database to play with, but I would expect this to remove the index since that’s what it does on postgres. You can use manage.py sqlmigrate to view the SQL that gets run as a part of the migration.

11

ok well I dropped the whole database, and imported again. Ran that migration again.

I think HeidiSQL was caching stuff it should not have… the model1 index is properly gone.

Apologies guys. :slight_smile:

Good thing: query went from 0.44ish seconds to 0.05 seconds. Almost an order of magnitude better and I think (hope!) it will continue to scale much more nicely.

Thanks :slight_smile: