Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5882

Research statistics and partitioning methods available for innodb table

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Done
    • None
    • N/A
    • MariaDB Server
    • None

    Description

      Consider the use case when MCS runs this query.

      CREATE TABLE  IF NOT EXISTS  t1( a DECIMAL(12, 2), b int ) ENGINE=innodb;
      INSERT INTO t1 SELECT ROUND(RAND() * 1000000, 2),ROUND(RAND() * 10000, 0) FROM seq_1_to_32000000;
       
      CREATE TABLE IF NOT EXISTS t2  ( a DECIMAL(12, 2), b int ) ENGINE=COLUMNSTORE;
      INSERT INTO t2 SELECT ROUND(RAND() * 1000000, 2),ROUND(RAND() * 10000, 0) FROM seq_1_to_2;
       
      select b, sum(a) from t2 where 0=1 group by b
        UNION ALL
        select b, sum(a) from t1 group by b;
      

      The Innodb table data can be partitioned to retrieve partitions in parallel.

        select b, sum(a) from t2 where 0=1 group by b
        UNION ALL
        select b, sum(a) from t1 where b between 0 AND 2500 group by b  UNION ALL
        select b, sum(a) from t1 where b between 2501 AND 5000 group by b  UNION ALL
        select b, sum(a) from t1 where b between 5001 AND 7500 group by b  UNION ALL
        select b, sum(a) from t1 where b between 7501 AND 10000 group by b ;
      

      This issue covers methods to find out about the currently available partitioning methods or suggest a new ones based on statistics available for innodb table.

      Attachments

        Issue Links

          Activity

            There are no comments yet on this issue.

            People

              sergey.zefirov Sergey Zefirov
              drrtuy Roman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.