A CRM database that was snappy at launch often becomes sluggish a year or two later, with no change in code. The reason is almost always data growth meeting queries that were never designed for it. A search that scanned a few thousand rows without complaint now scans millions. The cure is a repeatable method rather than a bag of tricks, and it begins with finding the right query.

One caution before anything else: if your CRM is a commercial product, the vendor may restrict direct changes to its schema, and changes made outside supported channels can break upgrades or support agreements. Check the vendor’s current documentation and consult them before adding indexes to a product database. The approach below applies fully to databases you own and, in principle, to custom tables and reporting copies.

Step One: Find the Real Offenders

Do not guess which queries are slow. Ask the database. Most engines can log statements that exceed a time threshold and expose statistics on the most expensive queries by total time, average time and call count. Look in your engine’s documentation for slow query logging and built-in statistics views.

Then rank by total cost, not just the single slowest example. A query that takes a moderate amount of time but runs many thousands of times a day can cost far more than a rare slow report. The answer to “which query should I fix first?” is usually the one consuming the most cumulative time.

Also correlate with complaints. If users report slowness opening a particular screen, find the queries that screen triggers, perhaps by timing a request while watching the database activity.

Step Two: Read the Execution Plan

For a suspect query, ask the database how it intends to run it. Every mainstream engine offers an explain facility that shows the plan. You are looking for a few warning signs:

• A full scan of a large table where you expected a targeted lookup.
• A sort over a large number of rows that spills to disk.
• A join that processes far more rows than the final result needs.
• Estimates wildly different from actual row counts, which often means outdated statistics.

Read the plan before touching anything. Knowing why a query is slow tells you whether an index will help or whether the real problem is somewhere else.

Step Three: Choose the Fix That Matches the Cause

A missing or unsuitable index. If a query filters or joins on columns without a helpful index, the database reads far more than it should. An index on the filtered columns, ordered to match how the query uses them, can change the work dramatically. Composite indexes depend on column order, so the equality conditions generally come before range conditions, but verify against your engine’s guidance.

A query that cannot use the index it has. Wrapping an indexed column in a function, comparing values of different types, or beginning a text search with a wildcard can prevent index use. Rewriting the condition often fixes this without any schema change.

Outdated statistics. The optimizer chooses plans based on statistics about data distribution. After large imports or deletions, refresh them and see whether the plan improves.

Fetching too much. Selecting every column and every row when the screen displays twenty is wasteful. Limit the columns, paginate results, and filter in the database rather than in application code.

Too many round trips. Sometimes each query is quick but a screen runs hundreds of them. The remedy is to combine them, not to tune each one.

The Cost of Indexes

Indexes are not free. Each one takes storage and slows inserts, updates and deletes, because the database must maintain it. A CRM with heavy write activity, such as constant imports and integration updates, can suffer from over-indexing as much as from under-indexing.

So add indexes for demonstrated need, and review existing ones for redundancy. An index that duplicates the leading columns of another is a candidate for removal. Usage statistics in most engines show which indexes are never used. Take care with that evidence, though, since some indexes serve rare but important jobs such as month-end reporting.

Change Safely

Test every change on a staging copy with realistic data volumes, since a plan on a tiny dataset can differ completely from the plan on a full one. Create indexes during quiet periods, or use online creation features where your engine supports them, because building an index on a large table can lock it or consume significant resources. Keep a script to reverse each change, and record what you changed and why.

Diagnosis Quick Reference

What you see	Likely cause	First thing to try
Full table scan on a filtered query	Missing index	Index the filter columns, test on staging
Index exists but is ignored	Function on column or type mismatch	Rewrite condition
Plan estimates far from reality	Stale statistics	Refresh statistics
Fast query, but slow screen	Too many queries per request	Combine or cache
Imports getting slower over time	Too many indexes on write-heavy table	Review and drop unused indexes
Large sort spilling to disk	Missing index for ordering, or excess rows	Add supporting index, limit results

A Hypothetical Example

Imagine a contact search screen that has gradually slowed. Ranking queries by total time shows one statement dominating: it looks up contacts by an account identifier and sorts them by last activity date. The plan reveals a full scan of the contacts table followed by a large sort.

On a staging copy with production-like volume, the team adds a composite index covering the account identifier and the activity date. The new plan reads only that account’s rows, already in order. They apply the change in a quiet window, compare the query’s timing against its earlier numbers, and watch write performance for a few days to confirm imports were not noticeably affected. The change is documented with its rollback script.

The method mattered more than the index: measure, read the plan, change one thing, measure again.

Verify and Keep Watching

After any change, rerun the same measurement that identified the problem. If you cannot show an improvement, you have not fixed it, however plausible the theory. Repeat the slow query review on a schedule, perhaps monthly, because growth keeps moving the target.

Frequently Asked Questions

Should I index every column used in a filter? No. Each index adds maintenance cost on writes and uses storage. Index for specific, measured queries, and prefer composite indexes designed around how queries actually combine conditions.

Can I add indexes to my vendor’s CRM database? Not always safely. Some vendors prohibit or discourage schema changes, and upgrades can fail or remove them. Check the vendor’s current documentation and speak to support first, or use a reporting replica for heavy queries.

How often should statistics be refreshed? Many engines do it automatically, but large data loads can leave them stale. Refreshing after major imports or deletions is a sensible habit, according to your engine’s guidance.

Why is the same query fast on staging and slow in production? Usually differences in data volume, data distribution, concurrent load or statistics. Test with a realistic copy and compare execution plans between the two.

When is the problem not the database? When individual queries are fast but the application makes too many of them, or when slowness appears in the network or application tier. Measure at each layer before committing to a database fix.

Next Step

Turn on slow query logging or open your engine’s top-queries view, sort by total time, and read the execution plan for the single most expensive statement. That one query is your starting point.