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

View execution slower than underlying query

    XMLWordPrintable

Details

    Description

      Execution of a query on a view is up to 3 times slower in comparison to the underlying query even if there's no data within underlying tables.

      Root cause is function check_duplicate_names() in sql/sql_view.cc which tests for duplicate column-names within views or derived queries.

      It is invoked in selects accessing a view though this check has already been made at view-creation time.

      However the implementation in check_duplicate_names() can be improved to gain better performance: mariadbViewPerformance.patch
      This patch has been tested in 10.1.45 but should be applicable to subsequent releases as well.

      Testing of performance improvement can be done with attached SQL file test.sql and mysqlslap:

      mysql -h 127.0.0.1 -u root test < test.sql
      mysqlslap -h 127.0.0.1 -u root --create-schema=test -c 100 -q "SELECT * FROM views_perf;"
      

      Attachments

        1. test.sql
          131 kB
        2. mariadbViewPerformance.patch
          2 kB
        3. mapTest.patch
          5 kB

        Activity

          People

            Unassigned Unassigned
            bjaenichen Bernd Jänichen
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.