docs/en/explanation/implicit-indexes.rst
Ever noticed the DBAL creating indexes you did not remember asking for,
with names such as IDX_885DBAFAA76ED395? In this document, we will
distinguish three types of indexes:
user-defined indexes indexes you did ask for
DBAL-defined indexes indexes you did not ask for, created on your behalf by the DBAL
RDBMS-defined indexes indexes you did not ask for, created on your behalf by the RDBMS
RDBMS-defined indexes can be created by some database platforms when you create a foreign key: they will create an index on the referencing table, using the referencing columns.
The rationale behind this is that these indexes improve performance, for instance for checking that a delete operation can be performed on a referenced table without violating the constraint in the referencing table.
Here are some database platforms that are known to create indexes when creating a foreign key:
MySQL <https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html>_MariaDB <https://mariadb.com/kb/en/foreign-keys>_These platforms can drop an existing implicit index once it is fulfilled by a newly created user-defined index.
Some other will not do so, on grounds that such indexes are not always needed, and can be created in many different ways. They instead leave that responsibility to the user:
PostgreSQL <https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys>_SQLite <https://sqlite.org/foreignkeys.html#fk_indexes>_SQL Server <https://stackoverflow.com/questions/836167/does-a-foreign-key-automatically-create-an-index>_So why does the DBAL create this index on every platform, even the ones that neither require it nor create it themselves?
Because of how the DBAL compares schemas on MySQL and MariaDB. When the DBAL reads a table back, it cannot tell an index you added from one the database created for the foreign key: the metadata records nothing about who created it. So a table you defined without a backing index comes back with one. If the DBAL hadn't created it up-front, the comparison would report a change that isn't real: the migration would drop the index, the database would recreate it immediately, and the next comparison would report the same change again — a migration that never converges. Implicit indexes work around this limitation.
The DBAL's schema model is platform-agnostic, so it cannot apply this only to MySQL and MariaDB. The index ends up on every database, including the ones that never needed it.
This is a detail, but these indexes will be prefixed with IDX_, and
typically look like this:
.. code-block:: sql
CREATE INDEX IDX_885DBAFAA76ED395 ON posts (user_id)
The generated name fits within the platform's identifier-length limit.
In the case of MariaDB and MySQL, the creation of that DBAL-defined index will result in the RDBMS-defined index being dropped.
You can still explicitly create such indexes yourself, and the DBAL will notice when your index fulfills the indexing and constraint needs of the implicit index it would create, and will refrain from doing so, much like some platforms drop indexes that are redundant as explained above.