Field collection item table names in mysql

Question

I am building a feature which will conditionally need to output entity data. The entity type is a custom one I created, which is complex and implements multiple nested field collections.

My issue is that looping through entities using Entity::loadMultiple() is very slow, as each entity needs to use FieldCollectionItem::load() multiple times to get to the data it needs to output. Over 1000 entity instances this uses up a lot of resources and takes a long time to load.

I am trying to circumvent this by retrieving the data via a SQL query and joining tables together. I'm hitting a road block when retrieving data from field collection items with long machine names, as the data appears to be stored in tables with names like field_collection_item__5d28be6634, basically just "field_collection_item__" with a 10-character hexadecimal hash added to it.

This would not theoretically be a big issue, except it looks like all records for the same field collection item seem to be split up across many of these tables with no pattern or connection that I can discern.

How do I know which tables to retrieve my data from for any given parent field collection instance?

Edit: I have revisited this recently and thought some of these tables might be revision tables, but none of them include the "_r_" in the table name. I still cannot determine which tables to use as there seem to be multiple tables (not including revision tables) for one field collection item. Looping through entities and loading nested field collections is taking upwards of 5 minutes which is just too long for my users.

Answer 1

In Drupal 8 the field tables are created as [ENTITY_TYPE]__[FIELD_NAME] and [ENTITY_TYPE]_revision__[FIELD_NAME] in database. The [ENTITY_TYPE]__[FIELD_NAME] is a main field table where latest revision data of entity field is stored. Where as [ENTITY_TYPE]_revision__[FIELD_NAME] is field revision table where all revision data is stored for a field.

In your case I think your Drupal/Database has some issue, because of which the table name has random character (10 characters) hexadecimal in it. I'm not sure of exact reason of this, but I too have notice this sometime in my Drupal Site (only twice one in D7 and one in D8) not every time.

In such case, where you're unsure of which table is used for which field, you can use name and value column in key_value table to get field table mapping. The name column contains, the entity and field name and value column contains serialized array of field schema stored in blob type. The name column format is as below:

[ENTITY_TYPE].field_schema_data.[FIELD_NAME]

So if I've field named as field_test in field_collection_item entity, then the mapping will be stored as field_collection_item.field_schema_data.field_test in key_value table. Check the screenshot below:

Screenshot 1: key_value table showing record of field_test field.

Screenshot 2: value column data from key_value table for field_test field.

I know this is not a better solution but, if table name doesn't has field name in it you can use data in value column in key_value table, you can find which table is used for which field.

Hope this helps.