Uploaded image for project: 'Confluence Cloud'
  1. Confluence Cloud
  2. CONFCLOUD-10030

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

XMLWordPrintable

    • 5

    • Our product teams collect and evaluate feedback from a number of different sources. To learn more about how we use customer feedback in the planning process, check out our new feature policy.

      NOTE: This suggestion is for Confluence Cloud. Using Confluence Server? 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
          Igor Minar
        2. lower-func-performance.patch
          28 kB
          Igor Minar
        3. lower-func-performance.patch
          18 kB
          Igor Minar
        4. lower-func-performance.patch
          19 kB
          Igor Minar
        5. lower-func-performance.patch
          3 kB
          Ivan Benko [Atlassian]

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

            Katherine Yabut made changes -
            Workflow Original: JAC Suggestion Workflow [ 3416284 ] New: JAC Suggestion Workflow 3 [ 3614718 ]
            Status Original: RESOLVED [ 5 ] New: Closed [ 6 ]
            Monique Khairuliana (Inactive) made changes -
            Workflow Original: Confluence Workflow - Public Facing v3 [ 2253294 ] New: JAC Suggestion Workflow [ 3416284 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing v3 - TEMP [ 2149842 ] New: Confluence Workflow - Public Facing v3 [ 2253294 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing v3 [ 1904961 ] New: Confluence Workflow - Public Facing v3 - TEMP [ 2149842 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing v2 [ 1811468 ] New: Confluence Workflow - Public Facing v3 [ 1904961 ]
            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 Cloud*. Using *Confluence Server*? [See the corresponding suggestion|http://jira.atlassian.com/browse/CONFSERVER-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 is related to CONFSERVER-10030 [ CONFSERVER-10030 ]
            vkharisma made changes -
            Project Import New: Sat Apr 01 14:06:06 UTC 2017 [ 1491055566265 ]
            Katherine Yabut made changes -
            Katherine Yabut made changes -

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

                Created:
                Updated:
                Resolved: