Database Indexing

PDF

JIRA 3.0 and later creates database indices automatically when the underlying table is created in the database. This means that if you are doing a fresh install of JIRA 3.0 (or later) you do not need to create indices manually. If you are upgrading JIRA from an earlier version (e.g. JIRA 2.6) and do not wish to create the indices manually, please follow these instructions and recreate (drop and create) JIRA's database (or remove all tables in the database) AFTER successfully exporting your data and before doing the import into the new version of JIRA. Removing the database will force JIRA to recreate all tables in the database and hence create all required indices.

If you do not wish to drop and recreate JIRA's database, you can add the indices manually by running the SQL statements shown below.

The syntax for creating indices differs between databases, so consult your documentation for the your database. In addition, if you change the database tables or fields that you use in entitymodel.xml, you will need to change the shown SQL statements.

Below is the SQL for creating indices on PostgreSQL (you will probably need to alter this for your database):

      CREATE INDEX action_issue ON jiraaction (issueid, actiontype);

      CREATE INDEX chggroup_issue ON changegroup (issueid);

      CREATE INDEX chgitem_chggrp ON changeitem (groupid);

      CREATE INDEX cf_cfoption ON customfieldoption (CUSTOMFIELD);

      CREATE INDEX cfvalue_issue ON customfieldvalue (ISSUE, CUSTOMFIELD);

      CREATE INDEX attach_issue ON fileattachment (issueid);

      CREATE INDEX subscrpt_user ON filtersubscription (FILTER_I_D, USERNAME);
      CREATE INDEX subscrptn_group ON filtersubscription (FILTER_I_D, groupname);

      CREATE INDEX issue_key ON jiraissue (pkey);

      CREATE INDEX issuelink_src ON issuelink (SOURCE);
      CREATE INDEX issuelink_dest ON issuelink (DESTINATION);
      CREATE INDEX issuelink_type ON issuelink (LINKTYPE);

      CREATE INDEX linktypename ON issuelinktype (LINKNAME);
      CREATE INDEX linktypestyle ON issuelinktype (pstyle);

      CREATE INDEX node_source ON nodeassociation (SOURCE_NODE_ID, SOURCE_NODE_ENTITY);
      CREATE INDEX node_sink ON nodeassociation (SINK_NODE_ID, SINK_NODE_ENTITY);

      CREATE INDEX ntfctn_scheme ON notification (SCHEME);

      CREATE INDEX osgroup_name ON groupbase (groupname);

      CREATE INDEX mshipbase_user ON membershipbase (USER_NAME);
      CREATE INDEX mshipbase_group ON membershipbase (GROUP_NAME);

      CREATE INDEX osproperty_all ON propertyentry (ENTITY_NAME, ENTITY_ID);

      CREATE INDEX osuser_name ON userbase (username);

      CREATE INDEX sec_scheme ON schemeissuesecurities (SCHEME);

      CREATE INDEX sec_security ON schemeissuesecurities (SECURITY);

      CREATE INDEX prmssn_scheme ON schemepermissions (SCHEME);

      CREATE INDEX sr_author ON searchrequest (authorname);
      CREATE INDEX sr_group ON searchrequest (groupname);

      CREATE INDEX user_source ON userassociation (SOURCE_NAME);
      CREATE INDEX user_sink ON userassociation (SINK_NODE_ID, SINK_NODE_ENTITY);

      CREATE INDEX workflow_scheme ON workflowschemeentity (SCHEME);
    

Once you have created the index, you may need to tell your database to recompute its indices. For PostgreSQL, the command is vacuumdb -U username -z -v database-name. Consult your database documentation for your database specific command.