Improving ERP System Performance Through Database Indexing

During my time at Creating Next Concepts, Inc. (Creating Info), I worked on a performance tuning task for our in-house ERP system. One particular incident stands out as a clear reminder of how impactful proper database indexing can be.

Identifying the Bottleneck

For consecutive weeks, our database monitoring system was raising alerts. The Index vs Sequential Reads graph showed an alarming trend. Most queries were bypassing indexes, leading to costly sequential scans.

Digging deeper into our PHP logs, we traced the issue to a frequently called function. This function checks whether a payment reference number is unique across the entire Cash Invoice Payments table. Given the volume of data and the lack of an index on the relevant column, the query was scanning the full table and filtering out all active Cash Invoice Payments each time it ran. This severely degraded performance.

The Optimization Strategy

We implemented the following changes to address the issue:

Indexing. Added an index on the payment reference number, dramatically reducing lookup time.

Reducing Joins. Moved the status field from the Cash Invoice table into the Cash Invoice Payment table, eliminating an unnecessary join during uniqueness checks.

Composite Index. Created a composite index on the payment reference number AND status to further optimize filtering and querying.

We validated improvements using the Doctrine profiler in Symfony. Query execution times dropped significantly, and our system stabilized with no further alerts triggered.

This experience taught me that thoughtful databsae indexing, combined with minimizing joins and understanding access patterns, can yield major performance gains in any data-intensive application.