Advantages and disadvantages of indexed links or when to use them?

Question

Doctrine ORM

https://www.doctrine-project.org/projects/doctrine...

Actually, if there is a unique field, then you can work with indexed relationships.
In theory, this should speed up the selection. collection search will always be O(1
) each entity has its own unique UUID, is it worth indexing all links in general, or are there specific cases where this is really necessary?

Answer 1

It all depends on when and where you use it.
1. If you use front-end data, reports, and the like to read data, then it’s better to do it almost on simple SQL queries. Then even the indexes of the doctrine may not reach. In order not to write all queries in SQL format, you can use QueryBuilder from Doctrine.
2. If you are trying to optimize it all for modification (creation, deletion, editing), then this, in principle, is not particularly required.
Thus, we come to use the CQRS pattern and separate our code and repositories into Read and Modify. This separation allows you to significantly speed up read requests, since the data will not be mapped to objects, but will be returned as simple arrays. We will also make the code better by removing unnecessary heteros and links that are used only for Reading. Well, if we go quite further, we can separately apply caching to Reading and save our system from unnecessary load.
As for indexes specifically...
This option is used to create an indexed association in your entity (at the PHP level). It doesn't really affect the DB schema.
If you want to easily get an element from a collection (e.g. managers) you might want to have doctrine map your managers in such a way that some value of each manager is a key in an array (actually an ArrayIterator) so you can get it as $this ->managers[$someId].
Without this, you would have to iterate through the entire collection to find a manager with a specific ID.

Answer 2

Indexes take up space and increase insertion time. Think try different options