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

XMLWordPrintable

    • 5

      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
          3 kB
          Ivan Benko [Atlassian]
        2. lower-func-performance.patch
          19 kB
          Igor Minar
        3. lower-func-performance.patch
          18 kB
          Igor Minar
        4. lower-func-performance.patch
          28 kB
          Igor Minar
        5. lower-func-performance.patch
          34 kB
          Igor Minar

            Assignee:
            Matt Ryall
            Reporter:
            Anatoli
            Votes:
            11 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved: