Issue #3269526: API for mapping entities to remote sources

Direct query EXPLAIN

UUID field item (no index)

EXPLAIN SELECT * from node_field_data WHERE node_field_data.source_uuid = '5f81bdae-2f34-48f0-91d1-eb97fbd418f6';
+------+-------------+-----------------+-------+--------------------------------+--------------------------------+---------+-------+------+-------+
| id   | select_type | table           | type  | possible_keys                  | key                            | key_len | ref   | rows | Extra |
+------+-------------+-----------------+-------+--------------------------------+--------------------------------+---------+-------+------+-------+
|    1 | SIMPLE      | node_field_data | const | node_field__source_uuid__value | node_field__source_uuid__value | 131     | const |    1 |       |
+------+-------------+-----------------+-------+--------------------------------+--------------------------------+---------+-------+------+-------+
1 row in set (0.000 sec)

SourceItem (indexed)

EXPLAIN SELECT * from node_field_data WHERE node_field_data.source_id__id = 'a4b2dccc-ee92-45c2-9c15-8aba87b8b72e';
+------+-------------+-----------------+------+----------------------------------+----------------------------------+---------+-------+------+-----------------------+
| id   | select_type | table           | type | possible_keys                    | key                              | key_len | ref   | rows | Extra                 |
+------+-------------+-----------------+------+----------------------------------+----------------------------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | node_field_data | ref  | node_field__source_id__source_id | node_field__source_id__source_id | 258     | const |    1 | Using index condition |
+------+-------------+-----------------+------+----------------------------------+----------------------------------+---------+-------+------+-----------------------+
1 row in set (0.000 sec)

EXPLAIN SELECT * from node_field_data WHERE node_field_data.source_id__id = 'a4b2dccc-ee92-45c2-9c15-8aba87b8b72e' AND node_field_data.source_id__version = '20ef49af-78be-4a89-8fc0-c9a0c78de4a8';
+------+-------------+-----------------+------+----------------------------------+----------------------------------+---------+-------------+------+-----------------------+
| id   | select_type | table           | type | possible_keys                    | key                              | key_len | ref         | rows | Extra                 |
+------+-------------+-----------------+------+----------------------------------+----------------------------------+---------+-------------+------+-----------------------+
|    1 | SIMPLE      | node_field_data | ref  | node_field__source_id__source_id | node_field__source_id__source_id | 516     | const,const |    1 | Using index condition |
+------+-------------+-----------------+------+----------------------------------+----------------------------------+---------+-------------+------+-----------------------+
1 row in set (0.001 sec)

Entity query

UUID

$query = \Drupal::entityQuery('node')->condition('source_uuid', '5f81bdae-2f34-48f0-91d1-eb97fbd418f6');

Is SQL

EXPLAIN SELECT base_table.vid AS vid, base_table.nid AS nid
FROM node base_table
INNER JOIN node_field_data ON node_field_data.nid = base_table.nid
WHERE node_field_data.source_uuid LIKE '5f81bdae-2f34-48f0-91d1-eb97fbd418f6'

Result

+--+-----------+---------------+------+---------------------------------------------------------------------------+------------------------------+-------+----------------------+----+------------------------+
|id|select_type|table          |type  |possible_keys                                                              |key                           |key_len|ref                   |rows|Extra                   |
+--+-----------+---------------+------+---------------------------------------------------------------------------+------------------------------+-------+----------------------+----+------------------------+
|1 |SIMPLE     |node_field_data|range |PRIMARY,node_field__source_uuid__value,node__id__default_langcode__langcode|node_field__source_uuid__value|131    |NULL                  |1   |Using where; Using index|
|1 |SIMPLE     |base_table     |eq_ref|PRIMARY                                                                    |PRIMARY                       |4      |db.node_field_data.nid|1   |                        |
+--+-----------+---------------+------+---------------------------------------------------------------------------+------------------------------+-------+----------------------+----+------------------------+

SourceItem

ID only

$query = \Drupal::entityQuery('node')
  ->condition('source_id.id', 'a4b2dccc-ee92-45c2-9c15-8aba87b8b72e');

Is SQL

EXPLAIN SELECT base_table.vid AS vid, base_table.nid AS nid
FROM node base_table
INNER JOIN node_field_data ON node_field_data.nid = base_table.nid
WHERE node_field_data.source_id__id = 'a4b2dccc-ee92-45c2-9c15-8aba87b8b72e'

Result

+--+-----------+---------------+------+-----------------------------------------------------------------------------+--------------------------------+-------+----------------------+----+------------------------+
|id|select_type|table          |type  |possible_keys                                                                |key                             |key_len|ref                   |rows|Extra                   |
+--+-----------+---------------+------+-----------------------------------------------------------------------------+--------------------------------+-------+----------------------+----+------------------------+
|1 |SIMPLE     |node_field_data|ref   |PRIMARY,node__id__default_langcode__langcode,node_field__source_id__source_id|node_field__source_id__source_id|258    |const                 |1   |Using where; Using index|
|1 |SIMPLE     |base_table     |eq_ref|PRIMARY                                                                      |PRIMARY                         |4      |db.node_field_data.nid|1   |                        |
+--+-----------+---------------+------+-----------------------------------------------------------------------------+--------------------------------+-------+----------------------+----+------------------------+

ID && Source

$query = \Drupal::entityQuery('node')
  ->condition('source_id.id', 'a4b2dccc-ee92-45c2-9c15-8aba87b8b72e')
  ->condition('source_id.version', '20ef49af-78be-4a89-8fc0-c9a0c78de4a8');

Is SQL

EXPLAIN SELECT base_table.vid AS vid, base_table.nid AS nid
FROM node base_table
INNER JOIN node_field_data ON node_field_data.nid = base_table.nid
WHERE (node_field_data.source_id__id = 'a4b2dccc-ee92-45c2-9c15-8aba87b8b72e') AND (node_field_data.source_id__version = '20ef49af-78be-4a89-8fc0-c9a0c78de4a8')

Result

+--+-----------+---------------+------+-----------------------------------------------------------------------------+--------------------------------+-------+----------------------+----+------------------------+
|id|select_type|table          |type  |possible_keys                                                                |key                             |key_len|ref                   |rows|Extra                   |
+--+-----------+---------------+------+-----------------------------------------------------------------------------+--------------------------------+-------+----------------------+----+------------------------+
|1 |SIMPLE     |node_field_data|ref   |PRIMARY,node__id__default_langcode__langcode,node_field__source_id__source_id|node_field__source_id__source_id|516    |const,const           |1   |Using where; Using index|
|1 |SIMPLE     |base_table     |eq_ref|PRIMARY                                                                      |PRIMARY                         |4      |db.node_field_data.nid|1   |                        |
+--+-----------+---------------+------+-----------------------------------------------------------------------------+--------------------------------+-------+----------------------+----+------------------------+