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

plugin for returning random rows in a table

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: Plugins
    • Labels:

      Description

      Selecting random rows from a table is a historicity hard problem. This is because the distribution of row values isn't evident.

      "Workarounds" have existed for a while - http://jan.kneschke.de/projects/mysql/order-by-rand/ - fairly intrusive however.

      Using engine independent statistics these are available in mariadb https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/.

      To solve exposing a fake table it should be able to get a clean SQL to get random rows.

      Like https://mariadb.com/kb/en/mariadb/sequence/ this should be a table discover https://mariadb.com/kb/en/mariadb/table-discovery/.

      Mode of operations could be:

      ANALYZE TABLE xxx PERSISTENT FOR ALL;
      INSTALL SONAME "ha_rand";
      SELECT * FROM rand_xxx_col1_5;
      

      The select would return 5 random rows from the table xxx based on the distribution of col1.

      Its use a a full member should be returned:

      SELECT r.id, other.id FROM rand_xxx_col1_5 r JOIN other on r.id=other.id;
      

      Goals in order:

      1. Initial table discovery and returning tables
      2. Participation in optimizer negotiation
      3. Ensuring a unique result - probably desirable from an expected use however altering the table syntax to have a flag as part of the name for this
      4. Make the table modifiable

      I'm willing to mentor this, co-mentors with a good knowledge of the storage API and table discovery would be much appreciated

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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