When adding database indexes to EspoCRM fixes slow lists and reports
An index tells the database how to find rows on a field without scanning the whole table. In EspoCRM, adding one is the right fix when a list view, filter or report slows down because it sorts or filters on a field that the database has to scan for every request, and the table has grown enough that the scan is the bottleneck. It is not the right fix when the slowdown comes from the server, a small buffer setting, or a list view that joins many related entities. Before defining anything, confirm which screen or report is slow, which field it sorts or filters on, and whether the same query is slow for one user or for everyone. Those answers tell you whether an index targets the actual cost.
EspoCRM documents how to define database indexes for each entity type in its developer documentation, so the index is declared with the entity's metadata rather than added by hand in the database. The reader should review the EspoCRM DB indexes documentation for the exact definition format of the version in use, because the syntax is part of the entity definition and must match the installed release. Keep a record of each index you add, the field it covers and the screen or report that motivated it, so later upgrades and rebuilds do not leave you guessing why it exists.
Indexes are one tuning lever alongside server sizing, database buffer and sort settings, and changes to how users build list views and reports. Often the most effective change combines two of these, for example an index on the default sort field plus narrower filters in a saved list view. Plan to measure the slow query before and after the change on a copy of production data, and confirm with your database administrator that the extra write cost and storage are acceptable for the table. Ask your partner or internal team to document the index definition, the rebuild steps and the test results so the change can be repeated on a restored or upgraded instance.
Index options and when each fits
This table compares single-field indexes, composite indexes for a filter plus sort, and no-index fallbacks such as changing list view habits. Read each row to see when the option fits, what it costs to write and maintain, and what to check in your own instance before choosing it.
| Option | When it fits | Write cost and maintenance | What to verify before choosing |
|---|---|---|---|
| Single-field index | One field dominates a slow filter or default sort, and most queries use it alone | Low write overhead; one more index to rebuild and review when the entity changes | Confirm the slow query actually filters or sorts on this field, using the SQL log or database slow query output |
| Composite index | A common list view filters on one or more fields and sorts on another, and the pair repeats across users | Higher write overhead on every insert and update of indexed fields; more to maintain as layouts and filters change | Check the column order against the real query, and confirm that the leading column is the one filtered with equality |
| Index on a text or varchar field | A lookup by a short identifier-style value such as a code or external ID | Index size grows with field length; long text fields are a poor fit | Confirm the field length and whether searches use exact match or partial match, since partial match usually cannot use a standard index |
| Changing list view or default sort | The slow view can be narrowed, paged or re-sorted without losing the data users need | No database change and nothing new to rebuild; relies on user habits and layout discipline | Measure the list with and without the change, and confirm which users depend on the current sort |
| Narrowing report filters | Reports scan large entities because filters are broad or not tied to a date or owner field | No schema change; may reduce report scope and needs owner agreement | Review report definitions with report owners and confirm which filters are required for their decisions |
| Reducing joined fields in layouts | List views pull in many related entities, increasing query work regardless of indexes | No write cost; may require layout redesign and retesting for each affected team | Count related fields shown per list view and confirm which relations users actually need in the grid |
| No index (accept the scan) | The entity is small, the query runs rarely, or the table is expected to stay modest | No added cost; the scan cost grows with data volume | Confirm current row counts and whether the query is run by a background job or a user, since timing differs |
Defining and rebuilding indexes in entity metadata
This sequence takes one slow EspoCRM query from evidence to a tested index, so the database serves the sort, filter or report that needed it without changing the application's behavior. Each step ends with a check, and the last step confirms the index is in use.
Take a verified backup first
The administrator or platform engineer takes a full database dump and a copy of the EspoCRM files before any metadata change, following the backup procedure for the deployment. Restore the dump to a separate test instance or confirm the restore steps work on a copy. The step is done when the dump file opens and a test restore completes without errors, so a failed rebuild has a known way back.
Identify the slow query
Reproduce the slow list view, report or filter in a non-production copy and capture the SQL it runs. Use the EspoCRM SQL logging options if they are enabled for the instance, and enable the database slow query log with a threshold the team sets for itself. The output is a short list of statements with their table, the WHERE and ORDER BY columns and the observed execution time. Record the entity and field names exactly as they appear in the metadata.
Define the index in entity metadata
The developer adds the index definition to the entity's metadata file, using the format described in the EspoCRM DB indexes documentation, usually under the entity's custom metadata in the custom metadata directory. Name the index clearly and list only the fields the slow query filters or sorts on, in the order the query uses them. Keep the change in version control so it can be reviewed and reverted. The step is done when the JSON or PHP file passes a syntax check and the diff shows only the intended index.
Run the rebuild in the test instance
Clear the cache and run the rebuild from the admin panel or the command line, as described in the rebuild guidance for the installed version, so EspoCRM creates the index. Watch the rebuild log for errors, and note whether the rebuild also touches columns or collations. Confirm the instance loads its list views and admin pages afterward. The step is done when the rebuild completes without errors and the application responds normally.
Confirm the index exists in the database
Use the database client to list the indexes on the entity's table, for example with SHOW INDEX on MySQL or MariaDB or the equivalent catalog query on PostgreSQL. Compare the names and column order with the metadata definition. The step is done when the index appears with the expected columns and no duplicate index covers the same leading columns, since duplicates add write cost without benefit.
Verify the query plan uses the index
Run EXPLAIN on the captured statement and check whether the planner now selects the new index for the table access. Compare the plan and the timing with the baseline from the first capture. If the planner still scans the table, check the column order, the data types and whether the query filters on expressions that prevent index use. The step is done when the plan shows the index and the measured time improves on the test data, or when the team has a documented reason the index is not used.
Roll back if the rebuild or plan check fails
If the rebuild fails, the application errors, or the index does not help, revert the metadata change in version control, restore the backup taken in the first step to the test instance, and rerun the rebuild to confirm the original state. Do not drop indexes by hand on production without a tested restore plan. The step is done when the reverted metadata, the restored database and the rebuilt application all match the pre-change baseline.
Repeat the sequence in production with a change record
After the test instance passes, the operator schedules the same change in production, takes a fresh backup, applies the metadata, runs the rebuild and repeats the index and plan checks against live data. Record the query, the index definition, the rebuild output and the before and after plans in the change log. The step is done when production shows the index, the slow query runs with the new plan and the change record is complete for audit.
Operational risks when changing indexes on a live CRM
Adding an index to a running EspoCRM touches the database that every user, job and integration depends on. The cards below cover the risks that matter most, from locking during business hours to credentials handled during the change, and the mitigation for each.
Rebuild locking during business hours
Index creation on a large table can lock writes or slow reads while it runs, and the effect grows with table size. Schedule the change in a maintenance window agreed with sales and service leads, and confirm with your DBA how your engine (MariaDB, MySQL or PostgreSQL) builds the index online. Ask the vendor or your own tests whether the rebuild blocks the entity's list views during the operation. Set a rollback point before you start.
Write slowdown on heavy entities
Every index adds work to each insert and update. Entities such as Lead, Opportunity or activity records that are edited all day will see slower saves after an index is added, and workflows and scheduled jobs that write in bulk feel it most. Measure save times and job durations before and after the change on a staging copy, and keep only indexes that match queries you have actually seen in use.
Disk growth from new indexes
Indexes take their own storage, and on a large CRM with years of activity history that can be significant. Check free space on the database volume before the change and plan for growth in your backup set too, since larger databases take longer to dump and restore. Ask your team to record the table size before and after, so capacity planning uses real figures rather than estimates.
Failed rebuild leaving inconsistent schema
If a rebuild stops partway, the database can be left with a half-applied index or with metadata that no longer matches the schema. Take a full database dump and a copy of the EspoCRM files immediately before the change, and test restoring that backup on a separate host. Confirm the rebuild procedure and recovery steps in the EspoCRM documentation for your version before running it in production.
Database credentials exposed during changes
Index changes need an account with schema privileges, and those credentials are often shared, cached in shell history or left in CI variables. Use a named change account with rights limited to the change, rotate its password afterward, and keep the application database user separate. Record who held the credentials, when they were used and which host ran the commands in your change log.
Untracked changes and no audit trail
An index added by hand on one server, or defined in metadata that was never committed, makes the next upgrade or rebuild behave unpredictably. Keep index definitions in version-controlled custom entity metadata, record each change with a ticket, the query it serves and the approver, and review the same definitions in staging before production. This keeps your environments aligned and gives auditors evidence of controlled change.
Questions teams ask before adding EspoCRM indexes
Can our own team add indexes to EspoCRM without outside help?
Yes, in most cases an experienced team with database and EspoCRM administration skills can do it, because the index definitions live in entity metadata and the change is applied through EspoCRM's rebuild process. The harder part is knowing which indexes to add. Your team needs to identify slow queries from the database side, match them to the list views, filters and reports that run them, and test the result on a copy of production. If nobody on the team has done that analysis before, a partner can share the load, but the decision on what to index should still be yours. Confirm the approach against the EspoCRM DB indexes documentation for your version.
What makes indexing work take more effort?
Effort rises with the number of slow queries you need to resolve, the number of custom entities and fields involved, and how many extensions add their own list views, reports and workflows that query the same tables. Database size matters because index builds take longer and need more free disk space on larger tables. Access also drives effort: if you lack direct database access, staging copies or monitoring of slow query logs, the discovery phase grows. Ask any provider to break their estimate down by these drivers rather than by a single number.
How long does adding indexes usually take?
Duration depends on scope: a short list of known slow queries on a small instance is a much smaller job than a review of a heavily customized deployment with several extensions and large tables. No general duration is supported by the sources used for this page, so ask for a timeline built from your own query list, table sizes and the number of environments that must be tested. A good plan separates discovery, staging tests, the production change window and post-change checks, and states what each phase depends on.
What does the work need from our team?
Plan for four inputs: read access to the database and its slow query data, a staging environment that closely matches production, a named person who can approve schema changes, and a maintenance window for the production rebuild. Your team should also take a backup before any change and confirm how to restore it. Without staging, you cannot measure whether an index helps or slows writes, so treat that as a hard requirement. Ask the provider how they will document each index, the query it serves and the rollback step.
Could a new index slow down EspoCRM or cause data problems?
Yes, an index can help reads and still add cost to inserts and updates, and an unnecessary index uses storage and adds maintenance work. Indexes that do not match real filters or sorts are the most common waste. Changes made during business hours can also lock or slow tables while they build. Test each index against write-heavy actions such as imports and workflows, and confirm that your database engine and version handle the change as expected. Keep the rollback path ready before the change window opens.
When is it worth bringing in a partner for EspoCRM indexing?
A partner is worth considering when your team lacks database tuning experience, when slow queries span several custom entities or extensions, when you cannot get a staging copy, or when the change must land inside a tight maintenance window. DeployOpen plans, implements and documents self-hosted open-source platforms inside your own infrastructure, and most engagements are fixed-scope with the scope, deliverables and timelines agreed in writing before work starts. Ask for a written scope that names the queries covered, the test evidence and the handover steps.
What should we ask for in a quote for this work?
Ask the provider to itemize discovery of slow queries, staging tests, the production change, post-change checks, documentation and any retained support. Each line should say who performs the work and what is one-time compared with recurring, such as ongoing monitoring or periodic review after new custom fields or extensions are added. Also ask what the quote excludes, such as database server tuning, hardware changes or performance work outside indexes. A written scope gives you a fair basis for comparing your own team's effort with an outside option.
How to confirm an index actually helps
An index is only worth keeping if it changes the query plan for the views your users actually open. Before you create anything, record a baseline: the list view timings for the default sort and the most-used filters, and the run time of each report that matters, measured on the same records and at the same time of day. Capture the database's execution plan for the underlying SQL as well, which you can get from the MariaDB or PostgreSQL command line by prefixing the query with EXPLAIN. Look at which index the optimizer picks, how many rows it estimates it must scan, and whether a sort step is still performed in memory. Repeat the same captures after the index is defined and rebuilt. If the plan still scans the whole table or ignores the new index, the index is not doing the work you intended, and the cause is often a column order that does not match the filter or sort, or a query shape that the index cannot serve.
Test on data volumes that resemble production, because small test databases hide the problem. A table with a few thousand rows can show no difference at all, while a table with millions of activity or email records behaves very differently. Ask your team to confirm which entities hold the largest row counts in your instance and to build the baseline against a restored copy of production data, not a fresh demo install. Compare list view load times for common users with their typical filters and for the administrator view, since permissions add conditions to the query. Keep the before and after numbers, the database version and the EspoCRM version in the same change record so that a later upgrade can be compared fairly.
Watch the write path for regressions, since every extra index slows inserts and updates. Entities that receive frequent imports, email fetches, workflow updates or scheduled job writes are where an added index can cost the most. Measure how long a representative CSV import and a batch of workflow-driven updates take before and after the change, and check the slow query log for new entries once the change is live. Also confirm that background jobs still finish within the window your operations team expects. If a write-heavy job slows, weigh whether the read gain justifies it, or whether the index belongs on a narrower set of columns. Confirm each of these checks against your own volumes and workload, not against figures from another deployment.
How DeployOpen scopes and delivers EspoCRM index work
DeployOpen treats index work on Adding database indexes to EspoCRM as a scoped change to your running instance, not a one-off database command. We agree the fields, queries and reports in writing, build and test the index definitions, and hand over the metadata and the checks your team needs to keep them correct.
How we scope
- Inventory the list views, default sorts, filters and reports that users rely on most often
- Agree in writing which entities and fields are in scope and which are excluded
- Confirm the database engine and version in use before any index definitions are written
How we build
- Define indexes in entity metadata for the agreed fields, using the EspoCRM developer approach
- Take a database backup and run the rebuild in a staging copy before touching production
- Check the slow queries and the query plans again after the change to confirm the index is used
How we hand over
- Deliver the metadata changes, rebuild steps and before and after query evidence as documentation
- Walk your administrators through how to add an index safely when a new field or report appears
- Record which indexes exist, why they were added and which queries they serve
How we support
- Review index needs again when you add custom entities, fields or reports
- Retest key queries after EspoCRM upgrades or large data imports
- Ongoing support and upgrades are available as a retainer, agreed separately from the initial scope
