[Django]-MySQL query performance cliff on large table with two IN statements

by

3👍

✅

It looks like all 3 fields are foreign keys on the foo table. Only one index can be used so add an index which includes all 3 fields to your model so it is used.

class Meta:
    index_together = ["field1", "field2", "field3"]

Write performance will take a small hit but at least you will be able to query your data. You do not need an index for every combination, in the index I provided above a query on all fields, only field1 or (field1 and field2) will use the index (because all fields from left to right are used and MySql can just ignore the rest of the index). Personally, I have never seen write performance suffer so much that I regret putting several indexes as needed on a table. It will take several hours to add the index to 200 million rows.

Note that django automatically generates indexes for ForeignKey fields or joins would be painfully slow. That’s why your explain output says possible_keys: (3 keys), those are likely indexes on field1-3.

Databases do jump off cliffs and with a table of 200 million rows I am not surprised your database did. Indexes are of vital importance to making databases purrr.

👤dotcomly

Leave a comment