Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7836

ANALYZE FORMAT=JSON should provide info about GROUP BY

        Details

        • Type: Bug
        • Status: In Progress
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1
        • Fix Version/s: 10.1
        • Component/s: Optimizer
        • Labels:
        • Sprint:
          10.1.6-1, 10.1.6-2, 10.1.7-1

          Description

          At the very least, we need to know

          • how many groups we've got
          • whether the heap or myisam table was used.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt

                • Major - Major loss of function.
                • Open

                Task - A task that needs to be done. MDEV-6995 EXPLAIN JSON and ORDER BY, GROUP BY, etc

                • Major - Major loss of function.
                • Open

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  An example that calls JOIN::make_simple_join() twice:

                  create table t5 (a int , b int) ;
create table t6 like t5 ;
create table t7 like t5 ;
insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
                      (2, -1), (3, 10) ;
insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
insert into t7 values (3, 3), (2, 2), (1, 1) ;

                  select count(distinct t5.b) as sum from t5, t6
                     where t5.a=t6.a and t6.b > 0 and t5.a <= 5
                     group by t5.a order by sum limit 1

                  This executes as follows:

                  1. create a tmp. table $TMP1 (without any keys)
                  2. run the join, put its output into $TMP1
                  3. create a temporary table $TMP2 (without any keys again)
                  4. use filesort() sort data from $TMP1 (produces an ordered stream)
                  5. read data from filesort result, do group-by-on-the-fly
                  and write into $TMP2.
                  // make_simple_join call.
                  6. use filesort(), read from $TMP2 and sort
                  7. read from filesort results and write to output.

                  Show
                  psergey Sergei Petrunia added a comment - An example that calls JOIN::make_simple_join() twice: create table t5 (a int , b int) ; create table t6 like t5 ; create table t7 like t5 ; insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7), (2, -1), (3, 10) ; insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ; insert into t7 values (3, 3), (2, 2), (1, 1) ; select count(distinct t5.b) as sum from t5, t6 where t5.a=t6.a and t6.b > 0 and t5.a <= 5 group by t5.a order by sum limit 1 This executes as follows: 1. create a tmp. table $TMP1 (without any keys) 2. run the join, put its output into $TMP1 3. create a temporary table $TMP2 (without any keys again) 4. use filesort() sort data from $TMP1 (produces an ordered stream) 5. read data from filesort result, do group-by-on-the-fly and write into $TMP2. // make_simple_join call. 6. use filesort(), read from $TMP2 and sort 7. read from filesort results and write to output.
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  MDEV-6995 has info about research done for EXPLAIN=JSON on this topic

                  Show
                  psergey Sergei Petrunia added a comment - MDEV-6995 has info about research done for EXPLAIN=JSON on this topic

                    People

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      psergey Sergei Petrunia
                    • Votes:
                      1 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated:

                        Agile