Support Center
Explore, Analyze & Document your Code

How to Find Missing Indexes in Your Database

What Is Indexing?

In database optimization, indexing creates a data structure that accelerates data retrieval.

An index is typically created on a column to speed up operations like joins, searches, filters, and sorting. Without an index, the database engine performs a ("full table scan"), which is resource-intensive and slow.

Indexes help the database quickly locate and access relevant data, significantly reducing query execution time and improving overall performance.

Benefits of Using Indexes

  • Improved query performance: Indexes accelerate data retrieval when columns are used in WHERE, HAVING, or GROUP BY clauses.
  • Faster sorting: Indexes boost performance when the column is used in ORDER BY clause.
  • Optimized joins: Table joins run faster by quickly locating matching rows.
  • Reduced disk I/O: Fewer data blocks are read, minimizing resource usage.
  • Efficient aggregations: Functions like COUNT, SUM, and AVG perform faster when relevant columns are indexed.

How Visual Expert Helps

Visual Expert analyzes your database schema, application code, and the SQL queries used throughout.

It automatically:

  • Identifies columns used in WHERE, GROUP BY, HAVING, or ORDER BY clauses.
  • Checks whether these columns are already indexed.
  • Generates a list of missing indexes to help you optimize query performance.

How to Find Missing Indexes Using Visual Expert

Prerequisites

  • Use Visual Expert 2025 (Desktop or Web client).
  • Include index definitions (CREATE INDEX ...) in your VE project:
    • Via SQL files, or
    • By connecting Visual Expert directly to your database.

Choose a Scanning Strategy

Depending on your needs, you can choose to check:

  1. The entire database
    Best for small databases or when most indexes already exist. Otherwise, you may end up with a large result that is difficult to use.
  2. A selection of tables
    Recommended for checking certain parts of the data model or optimizing certain slow features.
  3. A selection of code objects
    Recommended for checking that new/modified code does not introduce SQLs that require new indexes. Also, for inspecting performance-critical features.

1. Check the Entire Database

  1. Open Visual Expert.
  2. Select [Tables] at the root of the tree view.
  3. In the navigation bar, click [Missing Indexes].

    Columns requiring a new index are listed in a container hierarchy.
Check missing indexes for all Tables

Optional - Verify a specific column:

  • Select the column in the tree view.
  • In the navigation bar, go to Cross-references → Impact Analysis (tree).
  • The objects and queries referencing this column are listed.
  • Select the objects or queries to view the exact references—some of them will belong to WHERE, GROUP BY, HAVING, or ORDER BY clauses.

Next Steps:

  • Share the missing index list with your DBA.
  • Once indexes are created, re-run the analysis to validate improvements.
  • Repeat regularly to maintain optimal performance.
Verify missing indexes in table columns

2. Check a Selection of Tables

Visual Expert will check if an index should be created for each table selected.

  1. Open Visual Expert.
  2. Search for the tables you want to analyze:
    • Desktop Client: Go to the [Search] tab.
    • Web Client: Open the [Search criteria] in the blue ribbon.
  3. Enter your criteria ["Component name" contains "cust"].
  4. In the field [Search into], select "tables".
  5. Click [Search].
Find missing indexes in selected tables
  1. A new tab is created, with the tables matching your criteria.
    Tables found with missing indexes
  2. Select one or more tables (use Ctrl for multi-select).
  3. In the navigation bar, go to Code Review → Missing Indexes.
  4. Columns requiring a new index are listed in a container hierarchy.
    Check missing indexes for selected tables

If needed, you can double-check that a particular column requires an index:

  • Select the column in the tree view.
  • In the navigation bar, go to Cross-references → Impact Analysis (tree).
  • The objects and queries referencing this column are listed.
  • Select the objects or queries to view the exact references—some of them will belong to WHERE, GROUP BY, HAVING, or ORDER BY clauses.

Follow-up:

  • Pass the information to your DBA to create the missing indexes;
  • Once created, they will no longer appear in the analysis
  • Repeat regularly to keep your database optimized.
Verify missing indexes in table columns

3. Check a Selection of Objects

Visual Expert will analyze the SQL queries embedded in the selected objects, and check if the columns referenced by these queries require a new index.

  1. Open Visual Expert.
  2. Search for the objects you want to analyze:
    • Desktop Client: Go to the [Search] tab.
    • Web Client: Open the [Search criteria] in the blue ribbon.
  3. Enter some search criteria, for instance "Component name" contains "cust".
  4. In the field [Search into], select the type of object required, for instance "Procedures".
  5. Click [Search].
Find missing indexes in Procedures
  1. A new tab is created, with the objects matching your criteria.
    Missing Indexes found in Procedures
  2. Select one or more objects (use Ctrl for multi-select).
  3. Go to Code Review → Missing Indexes.
  4. Visual Expert shows columns requiring a new index in a container hierarchy.
    Check objects requiring indexes

Optional - Verify a particular missing index:

  • Select a column in the tree view.
  • The code using this column is displayed in the source code view
  • The exact reference is highlighted in the code for easy verification—this column may be used in WHERE, GROUP BY, HAVING, or ORDER BY clauses.

Next Steps:

  • Pass the information to your DBA to create the missing indexes;
  • Once created, they will no longer appear in the analysis
  • Repeat regularly to keep your database optimized.
Verify column objects missing indexes

 

More from Database Performance Optimization

Visual Expert, Code Analysis, Database Optimization, Code Performance, Missing Index

Visual Expert Featured Resources


Videos