Missing database index [#2827782]

Problem/Motivation

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.

Proposed resolution

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.

Remaining tasks

Discussion, patch, etc.

User interface changes

None.

API changes

None.

Data model changes

New index in the entity reference fields' tables.

Comment	File	Size	Author
#2	entityreference-missing-index-referencing-entity-relation-2827782-D7-2.patch	1.77 KB	ndobromirov
#2

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

15 November 2016 at 13:51

ndobromirov created an issue. See original summary.

Comment #2

ndobromirov CreditAttribution: ndobromirov commented 15 November 2016 at 14:39

File	Size
entityreference-missing-index-referencing-entity-relation-2827782-D7-2.patch	1.77 KB

Here is a patch with an update hook that fixed this for me for existing fields.

I've also tried to add the index on field's schema hook, but it did not worked, because of errors like this during node creation:

There was a problem creating field testing er fieldsd: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'field_testing_er_field_deleted' doesn't exist in table.

Code attempting to do this is available in the patch (but commented out).

TODOs:
1. Fix the field's schema to add the index on table creation. So it is still in needs work.

Comment #3

ndobromirov CreditAttribution: ndobromirov commented 15 November 2016 at 15:08

Status:	Active	» Needs work
Issue tags:		+Needs subsystem maintainer review

I've tracked this to core, please correct me if I'm wrong, but it seems to be impossible to define indexes based on field API columns and field level columns (like it's in this issue's case).

The issue is coming from the way hook_field_schema API is implemented and used. There are no later hooks and all indexes defined in it are expected to work only on it's columns (as you can see from the error in the prefixing).

- indexes: (optional) An array of Schema API indexes definitions. Only
columns that appear in the 'columns' array are allowed. Those indexes
will be used as default indexes. Callers of field_create_field() can
specify additional indexes, or, at their own risk, modify the default
indexes specified by the field-type module. Some storage engines might
not support indexes.

This is taken out from hook_field_schema($field) docks so it's confirming my suspensions.

Any options to move this forward will be appreciated...