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

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

    XMLWordPrintable

    Details

    • Feedback Policy:
      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 An updated workflow for server feature suggestions.
    • Internal Complexity:
      4
    • Internal Value:
      7
    • Support reference count:
      5

      Description

      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.

        Attachments

        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]

          Issue Links

            Activity

              People

              • Votes:
                11 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Last commented:
                  6 years, 2 weeks, 5 days ago