[Maria-developers] Review #3 for: MDEV-26278 Elimination: consider GROUP BY as unique key
Hi Oleg, Please fix compile errors on this platform: https://buildbot.mariadb.org/#/builders/146/builds/13126/steps/5/logs/stdio also please apply the attached diff that fixes the coding style and wording of comments (input and/or adjustments are welcome). After this, the patch is ok to push. The next steps are to create a branch with name like preview-10.10-mdev26278-table-elimination and to officially submit it for preview tree build and testing.
commit 7275bf4939248959b5017314d6b0393296831c0c (HEAD, origin/bb-10.9-MDEV-26278-look) Author: Oleg Smirnov <olernov@gmail.com> Date: Thu Feb 17 22:53:37 2022 +0700
MDEV-26278 Add functionality to eliminate derived tables from the query
Elimination of unnecessary tables from SQL queries is already present in MariaDB. But it only works for regular tables and not for derived ones.
Imagine we have a view: CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b
Due to "GROUP BY a, b" the values of combinations {a, b} are unique, and this fact can be treated as like derived table "v1" has a unique key on fields {a, b}.
Suppose we have a SQL query: SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b
1. Since {v1.a, v1.b} is unique and both these fields are bound to t2, "v1" is functionally dependent on t2. This means every record of "t2" will be either joined with a single record of "v1" or NULL-complemented. 2. No fields of "v1" are present on the SELECT list
These two facts allow the server to completely exclude (eliminate) the derived table "v1" from the query.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
Talking about input: I generally don't like code blocks like that: while (auto next_field= it++) 'cause - it's easy to overlook the iterator increment - you must remember that you cannot use iterator inside the loop since it's already shifted to next element - if you break the loop you must again remember that the iterator points to the next element and not the last one that was processed. But anyway this is a small detail, I've applied the patch. On Sun, Jun 12, 2022 at 7:18 PM Sergey Petrunia <sergey@mariadb.com> wrote:
Hi Oleg,
Please fix compile errors on this platform: https://buildbot.mariadb.org/#/builders/146/builds/13126/steps/5/logs/stdio
also please apply the attached diff that fixes the coding style and wording of comments (input and/or adjustments are welcome).
After this, the patch is ok to push.
The next steps are to create a branch with name like preview-10.10-mdev26278-table-elimination and to officially submit it for preview tree build and testing.
commit 7275bf4939248959b5017314d6b0393296831c0c (HEAD, origin/bb-10.9-MDEV-26278-look) Author: Oleg Smirnov <olernov@gmail.com> Date: Thu Feb 17 22:53:37 2022 +0700
MDEV-26278 Add functionality to eliminate derived tables from the query
Elimination of unnecessary tables from SQL queries is already present in MariaDB. But it only works for regular tables and not for derived ones.
Imagine we have a view: CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b
Due to "GROUP BY a, b" the values of combinations {a, b} are unique, and this fact can be treated as like derived table "v1" has a unique key on fields {a, b}.
Suppose we have a SQL query: SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b
1. Since {v1.a, v1.b} is unique and both these fields are bound to t2, "v1" is functionally dependent on t2. This means every record of "t2" will be either joined with a single record of "v1" or NULL-complemented. 2. No fields of "v1" are present on the SELECT list
These two facts allow the server to completely exclude (eliminate) the derived table "v1" from the query.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
-- Best regards, ------------------------- *Oleg Smirnov* Sr. Software Engineer MariaDB Server Team
participants (2)
-
Oleg Smirnov -
Sergey Petrunia