RubyMine 2023.3 Help

Indexes

A database index is a structure that is used to speed up locating and accessing operations in a database table. By using indexes, you decrease the number of disk accesses that are required when a query is processed. You can create indexes for one or more columns of a database table.

Indexes can be seen in the Database tool window. You can see a reference on node and object icons in the Data sources and their elements chapter of Database tool window topic.

For the table column icons, refer to Possible icon combinations for columns.

Create an index

  1. In the Database tool window ( View | Tool Windows | Database) , expand the data source tree until the nodes of columns.

  2. Right-click the table or column node and select New | Index.

  3. In the dialog that opens, enter the name of your index in the Name field.

  4. In the Columns pane, click the Add button (the Add button).

  5. In the Column Name field, specify the name of the column that you want to add to the index.

  6. In the Preview pane, you can view and change the generated SQL code.

  7. Click OK.

Create an index

Productivity tips

Modify templates for generated index and key names

When you create indexes, and primary and foreign key constraints, their default names are generated according to corresponding templates. For a primary key, for example, the template is {table}_{columns}_pk.

  • To view and modify these templates, open the settings Control+Alt+S and navigate to Editor | Code Style | SQL | General. Click the Code Generation tab.

    The templates can contain variables and text. When you generate a name, the specified text is reproduced literally. For example, when you apply the {table}_pk template in the actor table, the generated name of the primary key will be actor_pk.

    To see information about variables and their usage, click a field and press Control+Q.

    {unique?u:} checks if the index is unique and inserts the corresponding sequence of characters. If the index is unique, the template generates a name with the sequence of characters specified between ? and :. For the {unique?u:} template, it is u. If the index is not unique, the sequence between : and } is inserted. For the {unique?u:} template, it is nothing.

    Example

    You have the persons table with columns FirstName and LastName. The {table}_{columns}_{unique?u:}index template generates the following name for the not unique index: persons_FirstName_LastName_index.

    Modify templates
         for generated index and key names
Last modified: 04 December 2023