Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-10030

db2: queries that use 'lower' do not use index because of case sensitivity

XMLWordPrintable

    • 5
    • We collect Confluence feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      NOTE: This suggestion is for Confluence Server. Using Confluence Cloud? See the corresponding suggestion.

      Most of the queries against CONTENT table use 'lower' db function for a title field. The goes for (space) key field in SPACES table.
      We do it to make a case insensitive comparison, however it means that db2 does not use the existing indexes for those fields and performs a full table scan.
      This results in a poor performance.

      This can be applicable to other database too, but it is not verified. Also other databases may have global 'case sensitivity' flag.

        1. lower-func-performance.patch
          34 kB
        2. lower-func-performance.patch
          28 kB
        3. lower-func-performance.patch
          18 kB
        4. lower-func-performance.patch
          19 kB
        5. lower-func-performance.patch
          3 kB

            [CONFSERVER-10030] db2: queries that use 'lower' do not use index because of case sensitivity

            Sen Geronimo made changes -
            Workflow Original: JAC Suggestion Workflow 4 [ 3568261 ] New: JAC Suggestion Workflow 3 [ 4332848 ]
            Katherine Yabut made changes -
            Workflow Original: JAC Suggestion Workflow 2 [ 3178045 ] New: JAC Suggestion Workflow 4 [ 3568261 ]
            Status Original: RESOLVED [ 5 ] New: Closed [ 6 ]
            Katherine Yabut made changes -
            Workflow Original: JAC Suggestion Workflow [ 3036544 ] New: JAC Suggestion Workflow 2 [ 3178045 ]
            Owen made changes -
            Workflow Original: Confluence Workflow - Public Facing v4 [ 2523456 ] New: JAC Suggestion Workflow [ 3036544 ]
            Rachel Lin (Inactive) made changes -
            Workflow Original: Confluence Workflow - Public Facing v3 [ 2258229 ] New: Confluence Workflow - Public Facing v4 [ 2523456 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing v3 - TEMP [ 2194041 ] New: Confluence Workflow - Public Facing v3 [ 2258229 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing v3 [ 1922765 ] New: Confluence Workflow - Public Facing v3 - TEMP [ 2194041 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing v2 [ 1760844 ] New: Confluence Workflow - Public Facing v3 [ 1922765 ]
            jonah (Inactive) made changes -
            Description Original: Most of the queries against CONTENT table use 'lower' db function for a title field. The goes for (space) key field in SPACES table.
            We do it to make a case insensitive comparison, however it means that db2 does not use the existing indexes for those fields and performs a full table scan.
            This results in a poor performance.

            This can be applicable to other database too, but it is not verified. Also other databases may have global 'case sensitivity' flag.             		New: {panel:bgColor=#e7f4fa}
              *NOTE:* This suggestion is for *Confluence Server*. Using *Confluence Cloud*? [See the corresponding suggestion|http://jira.atlassian.com/browse/CONFCLOUD-10030].
              {panel}

            Most of the queries against CONTENT table use 'lower' db function for a title field. The goes for (space) key field in SPACES table.
            We do it to make a case insensitive comparison, however it means that db2 does not use the existing indexes for those fields and performs a full table scan.
            This results in a poor performance.

            This can be applicable to other database too, but it is not verified. Also other databases may have global 'case sensitivity' flag.
            jonah (Inactive) made changes -
            Link New: This issue relates to CONFCLOUD-10030 [ CONFCLOUD-10030 ]

              matt@atlassian.com Matt Ryall
              akazatchkov Anatoli
              Votes:
              11 Vote for this issue
              Watchers:
              9 Start watching this issue

                Created:
                Updated:
                Resolved: