Use virtual index instead of triggers

To recap, we need to JOIN to the entity we refer to. Most but not all entities are using integer keys so the choice is a string database column which can obviously store integers casted to string. But what happens if you try to filter them on an integer? Performance dies. To solve this, we have created a second column storing the value cast to integer and maintained the value via a trigger. This is no longer necessary as MySQL supports generated columns and indexes on them. For MariaDB's sake we can not make them VIRTUAL but the stored version is fine on both (see https://www.percona.com/blog/2016/03/04/virtual-columns-in-mysql-and-mar... ).

The following example shows how filtering a string column on an int value kills index use (ref: NULL) making a full table scan necessary (rows: 10) and warnings explaining the problem. And then we add a generated integer column which can use the index (ref: const) immediately finding the row requested (rows: 1) and no warnings.

mysql> CREATE TEMPORARY TABLE table1 (field1 VARCHAR(255), KEY(field1));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table1 VALUES ("1"),("2"),("3"),("4"),("5"),("6"),("7"),("8"),("9"),("10");
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM table1 WHERE field1 = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table1
   partitions: NULL
         type: index
possible_keys: field1
          key: field1
      key_len: 258
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 3 warnings (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use ref access on index 'field1' due to type or collation conversion on field 'field1'
*************************** 2. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use range access on index 'field1' due to type or collation conversion on field 'field1'
*************************** 3. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`table1`.`field1` AS `field1` from `test`.`table1` where (`test`.`table1`.`field1` = 2)
3 rows in set (0.00 sec)

mysql> ALTER TABLE table1 ADD field1_int INT UNSIGNED GENERATED ALWAYS AS ( field1 + 0 ) STORED;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE table1 ADD KEY(field1_int);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM table1 WHERE field1_int = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table1
   partitions: NULL
         type: ref
possible_keys: field1_int
          key: field1_int
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`table1`.`field1` AS `field1`,`test`.`table1`.`field1_int` AS `field1_int` from `test`.`table1` where (`test`.`table1`.`field1_int` = 2)
1 row in set (0.00 sec)

Ps. Yes, you need to use the ancient trick of using + 0 to cast to int, as far as I understand this, you can't use CAST() here for whatever reason.

As for PostgreSQL , we didn't need the trigger in the first place (my bad) instead we just amend the views data to join on the CAST (field_foo_target_id TO UNSIGNED) expression and just slam an index on the expression itself which has been supported since the dawn of time https://www.postgresql.org/docs/7.4/indexes-expressional.html

/me subscribes 🤓

Triggers are not supported on some popular hosting platforms such as Pantheon, so it would be great to move away from using triggers.

As a user whose host doesn't support triggers, I am somewhat hesitant to start using this module on the 1.x major release given that there's no clear upgrade path to 2.x.

On a scale of 1 to 10, how concerned should users be about this? Should users on hosting platforms that don't support triggers not use this module for fear of losing support in the future?

Thanks!

@jibran - thank you for that concise and clear answer in #9. I had the same concern as @luke.leber because we are hosted on Acquia for some projects. As I started reading through the various issues about this specific problem with triggers and hosting (which I know little about - except that Acquia still has docs up that say triggers might cause issues), your comment makes me feel better to go ahead with 1.x, knowing I haven't built my site into a corner.

I also opened #3276723: In case database triggers aren't supported, update target_id_int within Drupal as well as another option. Not sure if that option is better or worse than this one, so just opened that one for discussion.

There's a reason I didn't add PHP code to keep those columns in sync -- if anyone ever writes those tables then the results would be bad if things get out of sync. Not to mention PHP bugs. It's better to have the database make sure those two are never desync'd.

(As an aside, virtual index approach should be used by core instead of revision / non-revision tables but I would not even suggest that in the core queue, much less write it, these days. Those days are gone.)

I'm interested in replacing triggers for DER 2.x/4.x;

Sounds like we didn't need the trigger for PostgreSQL, doesn't even sound like hypothetical indexes are needed (postgresql speak for virtual indexes)

As for PostgreSQL , we didn't need the trigger in the first place (my bad) instead we just amend the views data to join on the CAST (field_foo_target_id TO UNSIGNED) expression and just slam an index on the expression itself which has been supported since the dawn of time https://www.postgresql.org/docs/7.4/indexes-expressional.html

Wondering how a patch may be made.

Hello - what's the recommended solution or workaround for this issue currently? Which version of the module doesn't use SQL Triggers? Is it still 1.x(3.x in D10) or has there been another update?

Yep odd numbered majors = no triggers