Fixing Database Performance Problems Caused by Unused Indexes in MariaDB

Adding an index is a common way to improve database performance. When a query takes too long, developers often create an index on the columns used in its WHERE, JOIN, or ORDER BY clauses. The query may become faster, so the index stays in the database.

The problem appears over time. New application features introduce more indexes, old queries are replaced, and some indexes are no longer used. A table that originally had three indexes might eventually have ten or fifteen, even though the application still relies on only a few of them.

Unused indexes are not usually the direct reason a SELECT query becomes slow. Their impact is more noticeable during INSERT, UPDATE, and DELETE operations because MariaDB must maintain the indexes when the underlying data changes. They also consume disk space and compete for memory used by frequently accessed database pages.

Removing unnecessary indexes can improve write performance and reduce resource consumption. However, deleting an index simply because it appears unused is risky. It may support a report that runs once a month, a rarely executed administrative query, or a foreign key constraint.

The safer approach is to measure index usage, understand the workload, test questionable indexes, and remove them only after confirming that the application does not depend on them.

Why Too Many Indexes Can Become a Performance Problem

An index helps MariaDB locate rows without scanning an entire table. For example, an index on customer_id can make it faster to retrieve orders belonging to a particular customer.

But every additional index has a maintenance cost.

Consider an orders table with several indexes:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_status VARCHAR(30),
    payment_status VARCHAR(30),
    created_at DATETIME,
    total_amount DECIMAL(10, 2),

    INDEX idx_customer (customer_id),
    INDEX idx_order_status (order_status),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created_at (created_at),
    INDEX idx_total_amount (total_amount)
);

Some of these indexes may be useful for the application's queries. Others might have been added during earlier development and never used by the current application.

When a new order is inserted, MariaDB must update the primary key and the applicable secondary indexes. When an indexed column changes, the corresponding index entries may also need to be updated.

The additional work can affect write throughput, transaction latency, storage consumption, and buffer pool efficiency.

There is also a maintenance cost. Larger index collections take more space to back up, restore, and maintain.

This does not mean that fewer indexes always produce better performance. The objective is to keep indexes that support real workload requirements and remove those that provide no meaningful benefit.

Find Out Which Indexes Are Actually Being Used

MariaDB provides index usage information through the User Statistics feature.

The feature includes the INFORMATION_SCHEMA.INDEX_STATISTICS table, which reports index usage. It is not enabled by default.

You can enable it dynamically:

SET GLOBAL userstat = ON;

To keep it enabled after a server restart, configure the MariaDB option file:

[mariadb]
userstat = 1

Once enabled, query the index statistics:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    ROWS_READ,
    QUERIES
FROM information_schema.INDEX_STATISTICS
ORDER BY ROWS_READ ASC;

The ROWS_READ column records the number of rows read through an index. The QUERIES column is available from MariaDB 11.5 and records how many queries used the index.

For example, a result might look like this:

Table	Index	Rows read	Queries
orders	idx_customer	850000	42000
orders	idx_order_status	120000	9000
orders	idx_payment_status	0	0
orders	idx_total_amount	0	0

These figures are illustrative.

The last two indexes deserve investigation, but the result does not prove that they can be deleted. Statistics are useful only when they cover a representative period of database activity.

If user statistics were enabled recently, the counters may not include earlier application activity. A reporting query that runs at the end of every month may not appear in the statistics collected during an ordinary working week.

For that reason, observe the database through normal business cycles before making a removal decision.

Check Index Definitions Before Removing Anything

Usage statistics tell you whether an index has been accessed. They do not explain why the index was created or whether another index can serve the same queries.

Start by listing the indexes on the table:

SHOW INDEX FROM orders;

You can also inspect the table definition:

SHOW CREATE TABLE orders;

Look at the indexed columns, their order, uniqueness, and any associated constraints.

For example, consider these two indexes:

INDEX idx_customer (customer_id),
INDEX idx_customer_status (customer_id, order_status)

The second index begins with customer_id. It may support queries that filter only by customer_id as well as queries that filter by both customer_id and order_status.

That does not automatically make the first index unnecessary. Its usefulness depends on the workload, index size, query plans, and whether a narrower index provides a meaningful advantage.

The reverse situation is also important. An index on order_status is not necessarily replaced by an index on payment_status and order_status. Column order affects how a composite index can be used.

Do not identify duplicate indexes by comparing only their names or the number of columns. Compare their actual definitions and the queries that depend on them.

Use EXPLAIN to Understand Query Dependencies

Before marking an index as unused, inspect the queries that could benefit from it.

MariaDB's EXPLAIN statement shows how the optimizer plans to execute a query.

For example:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 10025
  AND order_status = 'Pending';

Review fields such as:

possible_keys: Indexes the optimizer considers suitable.
key: The index selected for the execution plan.
rows: The estimated number of rows examined.
type: The access method used to retrieve rows.
Extra: Additional execution information, such as filtering or temporary operations.

If you want to inspect actual execution behavior, use ANALYZE:

ANALYZE
SELECT *
FROM orders
WHERE customer_id = 10025
  AND order_status = 'Pending';

Unlike EXPLAIN, ANALYZE executes the query and provides actual execution statistics. Use it carefully on production systems, particularly for expensive queries.

An index may also be useful to the optimizer for estimating cardinality even when it is not selected for a particular execution plan. This is another reason why a low usage count should not immediately lead to deletion.

Test an Index Before Dropping It

MariaDB 10.6 and later support ignored indexes.

An ignored index remains present and maintained, but the optimizer does not consider it when creating query execution plans. This makes it useful for testing whether the application can operate without a particular index.

For example, suppose idx_payment_status appears unused.

Instead of dropping it immediately, mark it as ignored:

ALTER TABLE orders
ALTER INDEX idx_payment_status IGNORED;

The optimizer will now behave as though the index does not exist.

Run the application's normal queries and monitor their execution plans and response times. Pay particular attention to reporting jobs, background workers, and less frequently executed operations.

If a query becomes slower, or an important operation stops performing acceptably, restore the index:

ALTER TABLE orders
ALTER INDEX idx_payment_status NOT IGNORED;

If the application continues to perform normally throughout a representative testing period, the index becomes a stronger candidate for removal.

This approach is safer than dropping an index immediately because restoring an ignored index is much simpler than rebuilding an index on a large production table.

Remember that an ignored index is still maintained during data modifications. Ignoring it tests the optimizer's dependency on the index; it does not measure the full write-performance improvement that would result from removing it.

Remove an Index After Validation

Once an index has been tested and confirmed unnecessary, it can be removed with DROP INDEX:

DROP INDEX idx_payment_status ON orders;

Alternatively, use ALTER TABLE:

ALTER TABLE orders
DROP INDEX idx_payment_status;

Before executing the statement, check whether the index is required by a constraint or another database dependency. In particular, indexes used to support foreign key relationships need special attention.

Also consider the size of the table. Index removal can involve storage-engine work and may affect performance while it runs. Check the expected locking and execution behavior for the MariaDB version and storage engine in use.

For a large production table, schedule the change appropriately and monitor the database during the operation.

After removal, run the relevant application queries again and compare their execution plans with the previous results.

Common Mistakes During Index Cleanup

A few mistakes can turn a useful optimization exercise into a production incident.

Removing an index after a short observation period

An index may be used only by a monthly report, quarterly process, or occasional administrative operation. A few days of statistics cannot represent every workload.

Collect usage information across relevant business cycles.

Assuming a composite index replaces every individual index

Column order matters. An index on (customer_id, order_status) can support certain queries that use customer_id as the leading column, but it cannot automatically replace an index that begins with order_status.

Check the actual query patterns and execution plans.

Ignoring indexes that support constraints

Some indexes have a role beyond application query performance. Review foreign keys, unique constraints, and other schema dependencies before removing an index.

Treating zero reads as proof of zero value

An index might not have been selected during the observation period, but the optimizer may still use its statistics. It might also support a workload that was not active during monitoring.

Use the ignored-index test before dropping it.

Removing too many indexes at once

If several indexes are removed together and performance deteriorates, identifying the responsible change becomes difficult.

Test and remove candidates individually where practical.

Monitor the Results After Cleanup

Index optimization should be treated as an ongoing database maintenance activity.

After removing an index, compare database behavior with the baseline collected before the change.

Useful measurements include:

Metric	What to observe
INSERT latency	Whether write operations complete faster
UPDATE latency	Whether modifications to indexed columns become less expensive
DELETE latency	Whether removing rows requires less index maintenance
Disk usage	Whether index storage decreases
Buffer pool activity	Whether frequently accessed pages remain available
Query latency	Whether important SELECT operations remain stable
CPU utilization	Whether the database performs less index maintenance work

Do not expect every metric to improve immediately. The result depends on the index size, write volume, workload, and storage engine.

A small unused index on a lightly updated table may have almost no measurable effect. Removing several large secondary indexes from a heavily updated table can make a more noticeable difference.

The goal is to validate the change with actual workload measurements rather than assuming that every removed index produces a significant improvement.

Conclusion

Unused indexes can gradually increase the cost of maintaining a MariaDB database. They consume storage and require additional work during inserts, updates, and deletes. Over time, an index collection that no longer matches the application's workload can become an unnecessary source of overhead.

MariaDB's User Statistics feature helps identify indexes with little or no recorded usage. EXPLAIN and ANALYZE help determine how queries use the remaining indexes, while ignored indexes provide a safer way to test whether a candidate can be removed.

The important part is to avoid treating usage statistics as an automatic deletion list. Observe the database across representative workloads, check constraints and composite index behavior, test candidates, and remove them one at a time.

A well-maintained index strategy is not about having the fewest possible indexes. It is about keeping the indexes that support real application requirements without making every data modification more expensive than it needs to be.