Add COLUMNLAYOUT index to the columnlayoutitem table

XMLWordPrintable

    • 0

      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])

            Assignee:
            Unassigned
            Reporter:
            Andriy Yakovlev [Atlassian]
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: