Database indexes

Proper database indexes can drastically improve a performance of list view loading, reports, search operations etc.

After changes you need to rebuild database. It's better to do from CLI to avoid timeout issue.

Command to run rebuild:

php rebuild.php

Defining indexes

Indexes are defined in metadata > entityDefs.

Example

For Lead entity type.

custom/Espo/Custom/Resources/metadata/entityDefs/Lead.json:

{

    "indexes": {
        "myIndexNameOne": {
            "columns": ["columnName"]   
        },
        "myIndexNameTwo": {
            "columns": ["columnNameOne", "columnNameTwo"]  
        },
        "myUniqueIndexName": {
            "type": "unique",
            "columns": ["id", "columnNameOne", "columnNameTwo"]  
        }
    }
}

Note: Column names should be in camel case.

You need to run rebuild after changes.

Forcing index usage in ORM

You need to specify an index name in useIndex param.

<?php
$query = $entityManager->getQueryBuilder()
    ->select()
    ->from($entityType)    
    ->where($whereClause)
    ->useIndex('myIndexNameOne')
    ->build();

$collection = $entityManager->getRDBRepository($entityType)
    ->clone($query)
    ->find();

Relationship table indexes

In entityDefs:

{
    "links": {
        "linkName": {
            "additionalColumns": {
                "role": {
                    "type": "varchar"
                }
            },
            "indexes": {
                "role": {
                    "columns": ["role"]
                }
            }
        }
    }
}