Load multiple cardinality fields with a smaller result set

Somewhat of an idea.

The reason we get the cartesian multiplication is because we don't join on delta, but we can't join on delta because we can't know which field table is going to have all the deltas we need to load all the deltas for all of the fields.

Have a somewhat test-passing branch that first queries to get the maximum delta of any field per chunk, and once it's got that field table, joins everything on the deltas from that field table.

This causes all the delta 0, 1, 2, 3, 4 etc. to be returned together instead of every combination of those deltas.

Getting that field is an additional query per chunk, that's still a maximum of two queries per chunk instead of a maximum of 30 queries per chunk but it feels wasteful.

Not implemented but what I think might work:

If we created a new table, it can have a single column, 'delta', starting from 0 and reaching 1024 or 8096 or whatever arbitrary delta limit we want to set. We could always join on that table because we'll know it'll be there. No extra query, but the efficiency would be the same. All we need is sequential integers from 0-maximum in there.

Things I thought about before/after this that probably would not work at all:

1. UNION / UNION ALL - this requires all columns to be of the same type and the same number of columns, which is not the case for field tables. If we group field table by type we might be able to workaround that, but an entity with three multiple cardinality fields of three types would not benefit at all. Also not sure UNION performance would be better than a join

2. A temp table doing the same thing as the one added here - we'd have to constantly check if the table exists or not and recreate it.

3. MySQL sequence object - not something you can join against

4. GROUP BY - doesn't work because we'd want to group by delta and we don't have a reliable delta field to group on

OK this is green.

The extra table is a bit painful, but per #4 I looked at multiple different approaches that wouldn't require actually making a new table and they all came up short. The actual code change here is not very much overall. Also the table allows us to remove the PHP sort on delta which was an annoyance of the previous approach.

The table will automatically get more rows (+100 more than absolutely required each time) when entities are saved for new sites.

For existing sites I set it to have 4000 rows with a $settings override. A quick poll in slack showed that sites can easily have over 100, or 'several hundred' for field deltas (commerce coupons and paragraphs were mentioned). I'd somewhat hope once a site goes over 4000 field deltas it will start to hit other limits (memory, max_post_vars etc.) first. We could always increase it, but if we did we'd need to chunk the insert in the update at some point.

Found a problem with this approach - it results in languages * rows in the entity_delta_join table - e.g. you first of all get the useful rows, then you get an endless parade of useless rows for all the deltas that don't match where all the field values are null. Once I added the update to create 4000 rows I discovered this...

Also I'm not entirely sure the original report in #3607938: Chunking of Op in loadFromDedicatedTables causes Cartesian multiplication: was 100% correct. What I think we get is fields * language * delta (one row for every valid delta of every field in each language), which is a lot of rows, but it's not $fields * fields * delta * language.

edit: what we can probably do is page the results. So do a range on entity_delta_join.delta 0-20, if there are more than 20 deltas, get another 20 rows. In the vast majority of situations the number of queries will be the same but the results will be capped.

Latest approach looks great, we still get the consistent deltas to join on but without the crime against databases which my approach would have introduced, being able to skip the join on the base table is a nice by-product.

Since this isn't any of my code any more, I think I'm good to RTBC here.

Also given this is 100% internal to the new methods we could probably backport to an 11.4 patch release too.

I'm fairly sure that the original 'cartesian multiplication' diagnosis on #3607938: Chunking of Op in loadFromDedicatedTables causes Cartesian multiplication was incorrect, so re-titling this issue.

Pretty sure what we had in 11.4.0 was fields * delta * language rows (e.g. a row for every field data in each language, whereas that issue claims fields * fields * delta * language rows (a row for every possible combination of field deltas).

However, that's still a lot more rows than we want to have.

Rebased for performance test conflicts.

@joachim I think that probably would have allowed my approach to work if we'd added it to the revisions table and updated all revisions on all entities to have it.

However @alexpott's UNION query in the latest MR sidesteps this entirely - the UNION query figures out the deltas, then we join on that UNION, giving us the minimum possible result set.

In the process of rebasing now.

I ran an EXPLAIN using one of the queries from the Umami performance test against an Umami database, and it looks good:

EXPLAIN SELECT delta_join.id AS id, delta_join.langcode AS langcode, node__field_ingredients.field_ingredients_value AS field_ingredients_value, node__field_ingredients.delta AS field_ingredients_delta, node__field_recipe_category.field_recipe_category_target_id AS field_recipe_category_target_id, node__field_recipe_category.delta AS field_recipe_category_delta, node__field_tags.field_tags_target_id AS field_tags_target_id, node__field_tags.delta AS field_tags_delta, node__layout_builder__layout.layout_builder__layout_section AS layout_builder__layout_section, node__layout_builder__layout.delta AS layout_builder__layout_delta FROM (SELECT node__field_ingredients.entity_id AS id, node__field_ingredients.langcode AS langcode, node__field_ingredients.delta AS delta FROM node__field_ingredients node__field_ingredients WHERE (node__field_ingredients.entity_id IN (1)) AND (node__field_ingredients.deleted = 0) UNION SELECT node__field_recipe_category.entity_id AS id, node__field_recipe_category.langcode AS langcode, node__field_recipe_category.d
elta AS delta FROM node__field_recipe_category node__field_recipe_category WHERE (node__field_recipe_category.entity_id IN (1)) AND (node__field_recipe_category.deleted = 0) UNION SELECT node__field_tags.entity_id AS id, node__field_tags.langcode AS langcode, node__field_tags.delta AS delta FROM node__field_tags node__field_tags WHERE (node__field_tags.entity_id IN (1)) AND (node__field_tags.deleted = 0) UNION SELECT node__layout_builder__layout.entity_id AS id, node__layout_builder__layout.langcode AS langcode, node__layout_builder__layout.delta AS delta FROM node__layout_builder__layout node__layout_builder__layout WHERE (node__layout_builder__layout.entity_id IN (1)) AND (node__layout_builder__layout.deleted = 0)) delta_join LEFT OUTER JOIN node__field_ingredients node__field_ingredients ON node__field_ingredients.entity_id = delta_join.id AND node__field_ingredients.langcode = delta_join.langcode AND node__field_ingredients.delta = delta_join.delta AND node__field_ingredients.deleted = 0 LEFT OUTER JOIN node__field_recipe_category node__field_recipe_category ON node__field_recipe_category.entity_id = delta_join.id AND node__field_recipe_category.langcode = delta_join.langcode AND node__field_recipe_category.delta = delta_join.delta AND node__field_recipe_category.deleted = 0 LEFT OUTER JOIN node__field_tags node__field_tags ON node__field_tags.entity_id = delta_join.id AND node__field_tags.langcode = delta_join.langcode AND node__field_tags.delta = delta_join.delta AND node__field_tags.deleted = 0 LEFT OUTER JOIN node__layout_builder__layout node__layout_builder__layout ON node__layout_builder__layout.entity_id = delta_join.id AND node__layout_builder__layout.langcode = delta_join.langcode AND node__layout_builder__layout.delta = delta_join.delta AND node__layout_builder__layout.deleted = 0 ORDER BY delta_join.delta ASC;

+------+--------------+------------------------------+--------+---------------+---------+---------+----------------------------------------------------------+------+----------------+
| id   | select_type  | table                        | type   | possible_keys | key     | key_len | ref                                                      | rows | Extra          |
+------+--------------+------------------------------+--------+---------------+---------+---------+----------------------------------------------------------+------+----------------+
|    1 | PRIMARY      | <derived2>                   | ALL    | NULL          | NULL    | NULL    | NULL                                                     | 30   | Using filesort |
|    1 | PRIMARY      | node__field_ingredients      | eq_ref | PRIMARY       | PRIMARY | 43      | delta_join.id,const,delta_join.delta,delta_join.langcode | 1    |                |
|    1 | PRIMARY      | node__field_recipe_category  | eq_ref | PRIMARY       | PRIMARY | 43      | delta_join.id,const,delta_join.delta,delta_join.langcode | 1    |                |
|    1 | PRIMARY      | node__field_tags             | eq_ref | PRIMARY       | PRIMARY | 43      | delta_join.id,const,delta_join.delta,delta_join.langcode | 1    |                |
|    1 | PRIMARY      | node__layout_builder__layout | eq_ref | PRIMARY       | PRIMARY | 43      | delta_join.id,const,delta_join.delta,delta_join.langcode | 1    |                |
|    2 | DERIVED      | node__field_ingredients      | ref    | PRIMARY       | PRIMARY | 5       | const,const                                              | 26   | Using index    |
|    3 | UNION        | node__field_recipe_category  | ref    | PRIMARY       | PRIMARY | 5       | const,const                                              | 1    | Using index    |
|    4 | UNION        | node__field_tags             | ref    | PRIMARY       | PRIMARY | 5       | const,const                                              | 2    | Using index    |
|    5 | UNION        | node__layout_builder__layout | ref    | PRIMARY       | PRIMARY | 5       | const,const                                              | 1    | Using index    |
| NULL | UNION RESULT | <union2,3,4,5>               | ALL    | NULL          | NULL    | NULL    | NULL                                                     | NULL |                |
+------+--------------+------------------------------+--------+---------------+---------+---------+----------------------------------------------------------+------+----------------+
10 rows in set (0.001 sec)

e.g. everything is indexed/const. The rows consulted matches the number of rows in the result, not any more than that. The query against the UNION uses a filesort, but that's only against the derived rows from the UNION (e.g. the deltas), not the entity/field table.

I don't have an 11.x site with lots of data and the right combination of fields at the moment to check timings against a populated db.

@scott_euser

The time savings are not necessarily in the actual query time, but the overall overhead of executing queries.

e.g.

- preparing the query in the database API
- sending the query to the database over the network
- receiving the query back from the database over the network
- iterating over the result set

This will be more noticeable when the database is on a separate server than the web heads too.

Having said that we hopefully would not see the individual query take longer than all the individual queries combined.

Other things to try if you have time:

1. Completely disable the query cache (or restart mysql etc.) before each run just to rule that out.
2. Try a mixture of populated and empty fields since this might have a bigger impact in that case.

Pushed an 11.x backport - just query/cache counts and a small query list conflict in StandardPerformanceTest

@scott_euser yeah obviously it depends on the production hosting but that's where I'd expect to see an improvement.

Also in general a few query reductions here or there have very little impact on time - if we allow 1ms per query, and we remove say 10 queries via this issue, then that's 10ms which can be margin of error on a lot of requests which might take 150ms-400ms in total.

However the cumulative reduction in queries and cache operations in 11.4 vs. 11.2 is 100-800 on some pages (keeping track of this in https://docs.google.com/spreadsheets/d/1TnDOzQwdiKHb0O-UKP9NA4kx5VpcdRfc...), which could work out at 100ms-800ms for those kinds of requests.

There's also a scaling benefit with MySQL in that most servers will have an absolute cap on the number of queries they can serve at any one time - network requests, CPU threads, whatever's the first thing to get saturated. And theoretically at least less queries running should mean more headroom before hitting those thresholds.