Skip to content

Instantly share code, notes, and snippets.

@spetrunia
Created December 2, 2022 17:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save spetrunia/83e541a47f04cd0445690718ad951e01 to your computer and use it in GitHub Desktop.
Save spetrunia/83e541a47f04cd0445690718ad951e01 to your computer and use it in GitHub Desktop.
=== Old execution (like before the patch) ===
update series set val=1; SELECT t, next_seq_value() r FROM t1 IGNORE INDEX(t) GROUP BY t HAVING r = 1 ORDER BY t1.u;
+------+------+
| t | r |
+------+------+
| 10 | 1 |
| 12 | 1 |
| 14 | 1 |
| 16 | 1 |
+------+------+
Here I can see the code use end_write(), Group-by implementation based on a temporary table with a group key.
The SP-func is invoked for every record.
+------+------+
| t | u |
+------+------+
* | 10 | 10 | sp_func()=1
| 11 | 11 | sp_func()=0
* | 12 | 12 | sp_func()=1
| 12 | 13 | sp_func()=0
* | 14 | 15 | sp_func()=1
| 15 | 16 | sp_func()=0
* | 16 | 17 | sp_func()=1
| 17 | 17 | sp_func()=0
+------+------+
Ok.
== New execution (after the patch) ==
update series set val=1;
SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t) GROUP BY t HAVING r = 1 ORDER BY t1.u;
+------+------+
| t | r |
+------+------+
| 10 | 1 |
| 12 | 1 |
| 15 | 1 |
| 17 | 1 |
+------+------+
The code uses end_write_group() - that is, it does group-by on the fly (one group follows other), and then writes the result into the temp.table
+------+------+
| t | u |
+------+------+
* | 10 | 10 | sp_func()=1
| 11 | 11 | sp_func()=0
* | 12 | 12 | sp_func()=1
| 12 | 13 | Note -- there is NO call to sp_func() here. The code sees we're still in the same group and exits.
| 14 | 15 | sp_func()=0
* | 15 | 16 | sp_func()=1
| 16 | 17 | sp_func()=0
* | 17 | 17 | sp_func()=1
+------+------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment