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

Type: Suggestion
Resolution: Duplicate
Fix Version/s: None
Component/s: Data Center - Database
Labels:

UIS:
0
Feedback Policy:

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"));

Workaround

Add index manually.

Example for MSSQL

CREATE NONCLUSTERED INDEX [idx_cli_columnlayout] ON [dbo].[columnlayoutitem] ([COLUMNLAYOUT])

is related to

JRASERVER-67735 Create index for columnlayoutitem table

Gathering Interest

was cloned as

JRASERVER-70003 Add role_type_param_pid index to the projectroleactor table

Gathering Interest

Matt Doar added a comment - 01/Apr/2021 5:09 PM

Must have been your twin

Matt Doar added a comment - 01/Apr/2021 5:09 PM Must have been your twin

Andriy Yakovlev [Atlassian] added a comment - 01/Apr/2021 2:15 PM

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 - 01/Apr/2021 2:15 PM 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] made changes - 01/Apr/2021 2:13 PM

Resolution		New: Duplicate [ 3 ]
Status	Original: Gathering Interest [ 11772 ]	New: Closed [ 6 ]

Andriy Yakovlev [Atlassian] made changes - 01/Apr/2021 2:11 PM

Link

New: This issue is related to JRASERVER-67735 [ JRASERVER-67735 ]

rmontag-ap added a comment - 01/Apr/2021 1:39 PM

Also reported with JRASERVER-67735

rmontag-ap added a comment - 01/Apr/2021 1:39 PM Also reported with JRASERVER-67735

SET Analytics Bot made changes - 12/Feb/2021 1:49 AM

UIS

Original: 1

New: 0

Andriy Yakovlev [Atlassian] made changes - 29/Apr/2020 9:55 AM

Labels

Original: database index pse-request

New: database database-index index pse-request

Andriy Yakovlev [Atlassian] made changes - 29/Apr/2020 9:55 AM

Labels

Original: database pse-request

New: database index pse-request

SET Analytics Bot made changes - 25/Nov/2019 12:16 AM

UIS

New: 1

Andriy Yakovlev [Atlassian] made changes - 23/Sep/2019 5:12 PM

Description

Original: h3. Problem Definition
Table {{columnlayoutitem}} is used for storing Fields schema configs, example:
{noformat}
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
...
{noformat}

Table config:
{noformat}
\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)
{noformat}

So the table doesn't have index for the *columnlayout* column.
{{columnlayout}} is being used during data loading:
{code:Java|borderStyle=solid}
com.atlassian.jira.issue.fields.layout.column.DefaultColumnLayoutManager#transformToColumnLayoutItems
...
final List<GenericValue> columnLayoutItemGVs = ofBizDelegator.getRelated("ChildColumnLayoutItem", columnLayoutGV, ImmutableList.of("horizontalposition ASC"));
{code}

h3. Suggested Solution

h3. Workaround
Add index manually.
* Example for MSSQL
{code:sql|borderStyle=solid}
CREATE NONCLUSTERED INDEX [idx_cli_columnlayout] ON [dbo].[columnlayoutitem] ([COLUMNLAYOUT])
{code}

New: h3. Problem Definition
Table {{columnlayoutitem}} is used for storing Fields schema configs, example:
{noformat}
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
...
{noformat}

Table config:
{noformat}
\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)
{noformat}

So the table doesn't have index for the *columnlayout* column.
{{columnlayout}} is being used during data loading:
{code:Java|borderStyle=solid}
com.atlassian.jira.issue.fields.layout.column.DefaultColumnLayoutManager#transformToColumnLayoutItems
...
final List<GenericValue> columnLayoutItemGVs = ofBizDelegator.getRelated("ChildColumnLayoutItem", columnLayoutGV, ImmutableList.of("horizontalposition ASC"));
{code}

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

h3. Workaround
Add index manually.
* Example for MSSQL
{code:sql|borderStyle=solid}
CREATE NONCLUSTERED INDEX [idx_cli_columnlayout] ON [dbo].[columnlayoutitem] ([COLUMNLAYOUT])
{code}

Assignee:: Unassigned

Reporter:: Andriy Yakovlev [Atlassian]

Votes:: 1 Vote for this issue

Watchers:: 2 Start watching this issue

Created:: 23/Sep/2019 3:53 PM

Updated:: 01/Apr/2021 5:09 PM

Resolved:: 01/Apr/2021 2:13 PM

Jira Data Center

Details

Description

Problem Definition

Suggested Solution

Workaround

Attachments

Issue Links

Forms

Activity

Collapse comment: Matt Doar added a comment - 01/Apr/2021 5:09 PM

Expand comment: Matt Doar added a comment - 01/Apr/2021 5:09 PM

Collapse comment: Andriy Yakovlev [Atlassian] added a comment - 01/Apr/2021 2:15 PM

Expand comment: Andriy Yakovlev [Atlassian] added a comment - 01/Apr/2021 2:15 PM

Collapse comment: rmontag-ap added a comment - 01/Apr/2021 1:39 PM

Expand comment: rmontag-ap added a comment - 01/Apr/2021 1:39 PM

People

Dates