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

filesort to support engines with slow rnd_pos

    XMLWordPrintable

    Details

      Description

      There are many engines that either do not support position()/rnd_pos() at all (ColumnStore) or these methods work very slowly (Archive, CSV).

      This is a limitation a server should be able to cope with, instead of forcing every such engine to implement an imperfect workaround.

      The method rnd_pos() is primarily needed for filesort. The server sorts sort keys / row "positions" pairs and then reads positions and retrieves corresponding rows.

      There are two techniques a server can use to avoid using rnd_pos() in filesort. First, filesort can store actual column values instead of positions, then it won't need to retrieve rows afterwards. This is enabled automatically, depending on the length of these "addon" columns. Second, OPTION_BUFFER_RESULT flag can force all results to go into a temporary table.

      So, the solution for not-rnd_pos-able engines could be, like, "force OPTION_BUFFER_RESULT unless filesort puts all fileds into the addon list".

      Additionally it'd make sense to adjust the addon heuristics to use addon fields more, comparing the cost with the cost of a temporary table.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              8 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.