django · Akash-Kumar-Sen · Jun 15, 2023 · Jun 25, 2023
diff --git a/django/db/backends/base/features.py b/django/db/backends/base/features.py
@@ -360,6 +360,9 @@ class BaseDatabaseFeatures:
     # Does the backend support unlimited character columns?
     supports_unlimited_charfield = False
 
+    # Does this backend use composite index to search by any prefix of its key?
+    composite_index_supports_prefix_search = True
+
     # Collation names for use by the Django test suite.
     test_collations = {
         "ci": None,  # Case-insensitive.

diff --git a/django/db/models/fields/related.py b/django/db/models/fields/related.py
@@ -1308,6 +1308,7 @@ def set_managed(model, related, through):
                 related_name="%s+" % name,
                 db_tablespace=field.db_tablespace,
                 db_constraint=field.remote_field.db_constraint,
+                db_index=not connection.features.composite_index_supports_prefix_search,
                 on_delete=CASCADE,
             ),
             to: models.ForeignKey(

diff --git a/tests/model_options/test_tablespaces.py b/tests/model_options/test_tablespaces.py
@@ -97,6 +97,9 @@ def test_tablespace_for_many_to_many_field(self):
         sql = sql_for_table(Authors).lower()
         # The join table of the ManyToManyField goes to the model's tablespace,
         # and its indexes too, unless DEFAULT_INDEX_TABLESPACE is set.
+        expected_through_indexes = (
+            1 if connection.features.composite_index_supports_prefix_search else 2
+        )
         if settings.DEFAULT_INDEX_TABLESPACE:
             # 1 for the table
             self.assertNumContains(sql, "tbl_tbsp", 1)
@@ -111,9 +114,11 @@ def test_tablespace_for_many_to_many_field(self):
         # The ManyToManyField declares no db_tablespace, its indexes go to
         # the model's tablespace, unless DEFAULT_INDEX_TABLESPACE is set.
         if settings.DEFAULT_INDEX_TABLESPACE:
-            self.assertNumContains(sql, settings.DEFAULT_INDEX_TABLESPACE, 2)
+            self.assertNumContains(
+                sql, settings.DEFAULT_INDEX_TABLESPACE, expected_through_indexes
+            )
         else:
-            self.assertNumContains(sql, "tbl_tbsp", 2)
+            self.assertNumContains(sql, "tbl_tbsp", expected_through_indexes)
         self.assertNumContains(sql, "idx_tbsp", 0)
 
         sql = sql_for_table(Reviewers).lower()
@@ -132,4 +137,4 @@ def test_tablespace_for_many_to_many_field(self):
         sql = sql_for_index(Reviewers).lower()
         # The ManyToManyField declares db_tablespace, its indexes go there.
         self.assertNumContains(sql, "tbl_tbsp", 0)
-        self.assertNumContains(sql, "idx_tbsp", 2)
+        self.assertNumContains(sql, "idx_tbsp", expected_through_indexes)
diff --git a/tests/schema/tests.py b/tests/schema/tests.py
@@ -2362,6 +2362,66 @@ def test_m2m(self):
     def test_m2m_custom(self):
         self._test_m2m(CustomManyToManyField)
 
+    def test_m2m_optimization_through_indexes(self):
+        class LocalAuthorWithM2M(Model):
+            name = CharField(max_length=255)
+            tags = ManyToManyField(Tag, related_name="authors")
+
+            class Meta:
+                app_label = "schema"
+                apps = new_apps
+
+        self.local_models = [LocalAuthorWithM2M]
+
+        # Create the table
+        with connection.schema_editor() as editor:
+            editor.create_model(Tag)
+            editor.create_model(LocalAuthorWithM2M)
+
+        through = LocalAuthorWithM2M._meta.get_field("tags").remote_field.through
+        indexes = [
+            constraint["columns"]
+            for constraint in self.get_constraints(through._meta.db_table).values()
+            if constraint["index"] or constraint["unique"]
+        ]
+        self.assertIn(["tag_id"], indexes)
+        self.assertIn(["localauthorwithm2m_id", "tag_id"], indexes)
+        if connection.features.composite_index_supports_prefix_search:
+            self.assertNotIn(["localauthorwithm2m_id"], indexes)
+        else:
+            self.assertIn(["localauthorwithm2m_id"], indexes)
+
+    def test_m2m_optimization_through_indexes_false(self):
+        # Mocking the variable to false to test expected behaviour
+        # As django has no DB that has the following set to false
+        # We can remove this test once django supports one such case
+        connection.features.composite_index_supports_prefix_search = False
+
+        class LocalAuthorWithM2MFalse(Model):
+            name = CharField(max_length=255)
+            tags = ManyToManyField(Tag, related_name="authors")
+
+            class Meta:
+                app_label = "schema"
+                apps = new_apps
+
+        self.local_models = [LocalAuthorWithM2MFalse]
+
+        # Create the table
+        with connection.schema_editor() as editor:
+            editor.create_model(Tag)
+            editor.create_model(LocalAuthorWithM2MFalse)
+
+        through = LocalAuthorWithM2MFalse._meta.get_field("tags").remote_field.through
+        indexes = [
+            constraint["columns"]
+            for constraint in self.get_constraints(through._meta.db_table).values()
+            if constraint["index"] or constraint["unique"]
+        ]
+        self.assertIn(["tag_id"], indexes)
+        self.assertIn(["localauthorwithm2mfalse_id", "tag_id"], indexes)
+        self.assertIn(["localauthorwithm2mfalse_id"], indexes)
+
     def test_m2m_inherited(self):
         self._test_m2m(InheritedManyToManyField)