Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-70002

Add COLUMNLAYOUT index to the columnlayoutitem table

XMLWordPrintable

    • 0
    • We collect Jira 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.

      Problem Definition

      Table columnlayoutitem is used for storing Fields schema configs, example:

      select * from columnlayoutitem;
  id   | columnlayout |  fieldidentifier   | horizontalposition
-------+--------------+--------------------+--------------------
 10300 |        10000 | issuetype          |                  0
 10301 |        10000 | issuekey           |                  1
 10302 |        10000 | summary            |                  2
 10303 |        10000 | assignee           |                  3
 10304 |        10000 | reporter           |                  4
 ...

      Table config:

      \d columnlayoutitem
             Table "public.columnlayoutitem"
       Column       |          Type          | Modifiers
--------------------+------------------------+-----------
 id                 | numeric(18,0)          | not null
 columnlayout       | numeric(18,0)          |
 fieldidentifier    | character varying(255) |
 horizontalposition | numeric(18,0)          |
Indexes:
    "pk_columnlayoutitem" PRIMARY KEY, btree (id)
    "idx_cli_fieldidentifier" btree (fieldidentifier)

      So the table doesn't have index for the columnlayout column.
      columnlayout is being used during data loading:

      com.atlassian.jira.issue.fields.layout.column.DefaultColumnLayoutManager#transformToColumnLayoutItems
...
 final List<GenericValue> columnLayoutItemGVs = ofBizDelegator.getRelated("ChildColumnLayoutItem", columnLayoutGV, ImmutableList.of("horizontalposition ASC"));

      Suggested Solution

      Add an extra index to the table to reduce DB load and improve loading time.

      Workaround

      Add index manually.

      • Example for MSSQL 
        CREATE NONCLUSTERED INDEX [idx_cli_columnlayout] ON [dbo].[columnlayoutitem] ([COLUMNLAYOUT])

            [JRASERVER-70002] Add COLUMNLAYOUT index to the columnlayoutitem table

            Matt Doar added a comment -

            Must have been your twin

            Matt Doar added a comment - Must have been your twin

            Andriy Yakovlev [Atlassian] added a comment -

            Thanks for reporting duplicate, funny part it's also reported by me (facepalm).
            Good that we have Jira to keep the history

            Andriy Yakovlev [Atlassian] added a comment - Thanks for reporting duplicate, funny part it's also reported by me (facepalm). Good that we have Jira to keep the history

            rmontag-ap added a comment -

            Also reported with JRASERVER-67735

            rmontag-ap added a comment - Also reported with  JRASERVER-67735

              Unassigned Unassigned
              ayakovlev@atlassian.com Andriy Yakovlev [Atlassian]
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: