Problem/Motivation

Slow views queries when entity_data.value is attached to a view with a relationship.

Steps to reproduce

Attach the entity_id value field to a view that references a relationship.

Proposed resolution

It seems that entity_id is a varchar, but other entity ids are int

Remaining tasks

User interface changes

API changes

Data model changes

Comments

thebarton_steven created an issue. See original summary.

lobsterr’s picture

lobsterr commented

Entity id can be not only int

lobsterr’s picture

lobsterr commented
Title: entity _id uses varchar » entity_id uses varchar
lobsterr’s picture

lobsterr commented

how many rows do you in the tables you are trying to join ?

thebarton_steven’s picture

thebarton_steven commented

Thousands. Pretty much any node query is slow because node entity ids are ints.

  • e649bc74 committed on 1.0.x 
    fix: #3507751 entity_id uses varchar

By: lobsterr
lobsterr’s picture

lobsterr commented
Status: Active » Fixed

I found a solution, The node ID is stored as a number, but entity_data.entity_id is stored as text. When MySQL compares a number with text, it converts the text to a number. For example, text IDs '1', '01', and '1suffix' can all compare equal to numeric ID 1. This conversion also prevents efficient use of the text index. CAST(... AS CHAR CHARACTER SET ascii) turns the node ID into matching text first. MySQL then compares text with text and can use the index efficiently.

I did some performance tests and I see boost up to 50 times

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.

  • 77bc705a committed on 1.0.x 
    fix: #3507751 Add Gitlab ci/cd file and add support of Drupal 12

By:...

  • e5a6d08d committed on 1.0.x 
    fix: #3507751 Fix phpcs, phpstan issues

By: lobsterr