Improve Order page load times with large amounts of logs

Good catch!

One question:

+    $schema['commerce_log']['indexes'] += ['source_entity_id' => ['source_entity_id']];

Wouldn't you want the index to cover both source_entity_id and source_entity_type, since they're always used in tandem?
Something like this:

+    $schema['commerce_log']['indexes'] += ['source_entity' => ['source_entity_id', 'source_entity_type']];

That is true only for now. We have an issue for adding logging to payments: #2845321: Add payment logging to orders.
There is no guarantee that sites aren't already using the API for other entity types. For those the index won't be completely precise, because IDs might be shared between entity types.

So i'd prefer #4 if we can prove that it doesn't cause problems for your site.

Given 330,000 orders and 2,250,000 log entries, queries on commerce_log were often taking over 500ms. With the index this patch provides, it became reasonably fast.

Before:

mysql> explain SELECT commerce_log.log_id AS log_id FROM commerce_log commerce_log    WHERE ((commerce_log.source_entity_id = 123456))      AND (commerce_log.source_entity_type = 'commerce_order')    ORDER BY commerce_log.created DESC;
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | commerce_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2156164 |     1.00 | Using where; Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT commerce_log.log_id AS log_id FROM commerce_log commerce_log 
    ->   WHERE ((commerce_log.source_entity_id = 123456)) 
    ->     AND (commerce_log.source_entity_type = 'commerce_order') 
    ->   ORDER BY commerce_log.created DESC;
Empty set (0.86 sec)

After:

mysql> EXPLAIN SELECT commerce_log.log_id AS log_id FROM commerce_log commerce_log    WHERE ((commerce_log.source_entity_id = 123456))      AND (commerce_log.source_entity_type = 'commerce_order')    ORDER BY commerce_log.created DESC;
+----+-------------+--------------+------------+------+---------------+---------------+---------+-------------+------+----------+----------------------------------------------------+
| id | select_type | table        | partitions | type | possible_keys | key           | key_len | ref         | rows | filtered | Extra                                              |
+----+-------------+--------------+------------+------+---------------+---------------+---------+-------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | commerce_log | NULL       | ref  | source_entity | source_entity | 136     | const,const |    1 |   100.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------------+------------+------+---------------+---------------+---------+-------------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT commerce_log.log_id AS log_id FROM commerce_log commerce_log    WHERE ((commerce_log.source_entity_id = 123456))      AND (commerce_log.source_entity_type = 'commerce_order')    ORDER BY commerce_log.created DESC;
Empty set (0.00 sec)

The patch looks good to me, though I was sure this was covered by #2907367: Add indexes to important fields for some reason.

I wonder if we could reuse our CommerceContentEntityStorageSchema introduced there. Curious about the 32 limitation on the index, won't that be potentially an issue, considering the column itself doesn't have a 32 character limit?

The update hook should probably take care of updating the entity type definition as well.

Ok you're right, the "entity_type" shouldn't be longer than 32 characters, but I believe we should restrict the column/field itself which removes the need for a limit on the index itself, going to work on a patch that does that and see if I can reuse CommerceContentEntityStorageSchema.

Revising the metadata, preparing for the 2.25 release.

Ok, so changing the maximum length of an existing base field was more complex than I thought, it took me several attempts to get it right and to get rid of the warnings on the status report page after the update, but I think I got it right this time.

Looks good to me. I didn't know about the entity type length limit, but fixing it in the table instead of having an index that doesn't match the field makes a lot more sense.

Here's a before and after the patch, showing it's as fast as expected after, same as in #10.

mysql> SELECT commerce_log.log_id AS log_id FROM commerce_log commerce_log WHERE ((commerce_log.source_entity_id = 123456)) AND (commerce_log.source_entity_type = 'commerce_order') ORDER BY commerce_log.created DESC;
Empty set (2.42 sec)

mysql> SELECT commerce_log.log_id AS log_id FROM commerce_log commerce_log WHERE ((commerce_log.source_entity_id = 123456)) AND (commerce_log.source_entity_type = 'commerce_order') ORDER BY commerce_log.created DESC;
Empty set (0.00 sec)

Committed! Thanks for your feedback with testing! I also tried applying the patch myself on an existing project as well to ensure there weren't errors reported in the status report page and it all looked ok!