The module allows for direct and reverse selection between entities that reference each-other.
When you start joining multiple entity types, the indexes becomes important part (especially in views).
So when you have the direct link, it's OK, as the primary key is used for the lookup.
The problems comes with the views relationship called "Referencing entity". It is making a join based on the following columns:
- deleted (0)
- entity_type - string
- *_target_id - referenced entity ID.
In this case the current schema on MySQL 5.5 (default for Ubuntu 14.04) can use only the 'deleted' field index. In most cases this is non-optimal, as it is very close to a full table in length.
In my case, the views query was covering ~1200 rows, when doing the join. Rows estimate taken from explain. The index used was delete, not the *_target_id one.
I managed to fix this with adding an index on deleted, entity_type, *_target_id fields, so views can be optimized for reverse queries.
Note that the columns order is important. I've tested with *_target_id first, and the query optimizer was picking the deleted index again.
I've tested locally the same query and it resulted in evaluation of 5 rows (based on explain), and also using the new index, instead of the deleted generic one.
Discussion, patch, etc.
None.
None.
New index in the entity reference fields' tables.
Needs work
1.0
Code
It affects performance. It is often combined with the Needs profiling tag.
It is used to alert the maintainer(s) of a particular core subsystem that an issue significantly impacts their subsystem, and their signoff is needed (see the governance policy draft for more information). Also, if you use this tag, make sure the issue component is set to the correct subsystem. If an issue significantly impacts more than one subsystem, use needs framework manager review instead.
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.