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

Updates to derived-with-keys, window functions determining records per key

    XMLWordPrintable

Details

    • Q2/2026 Server Development

    Description

      Enabling derived keys optimization for derived.col = const pushed conditions.

      Estimating records per key in derived key for the optimizer based on form and/or size of components of a derived table.

      Consider a derived table of the form

      SELECT ..., ROW_NUMBER ()  OVER (PARTITION BY c1,c2 order by ...)
      FROM t1, t2, t3 ...
      WHERE ...
      

      If the optimizer generates a key on this derived table because of a constraint being pushed into it, it currently will not consider key components of the form column = const

      We lift this constraint and add code to TABLE::add_tmp_key to search for a window function ROW_NUMBER() in our item list. From the partition list c1, c2 we can in infer an estimate of the number of rows we expect to see for each key value. The optimizer can then use this number to determine a better table join order.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            Johnston Rex Johnston
            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.