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

    Details

    • Type: Suggestion
    • Status: Resolved
    • Resolution: Won't Fix
    • Affects Version/s: 2.5, 2.5.1, 2.5.2, 2.5.3, 2.5.4, 2.5.5, 2.5.6, 2.5.7, 2.5.8, 2.6-dr1, 2.6.0, 2.6.1
    • Fix Version/s: None
    • Component/s: None
    • Last commented by user?:
      true
    • Internal Complexity:
      4
    • Internal Value:
      7
    • Support reference count:
      5

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

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