A billing system that runs fine at 200 tenants starts showing stress at 2,000 and falls apart at 20,000 if the database layer wasn't designed for volume. I've diagnosed performance issues in storage billing platforms where the nightly renewal run went from taking 4 minutes to taking 4 hours as the facility portfolio grew — and the root cause was almost always the same class of problems: missing indexes, full table scans on ledger tables, and no query plan awareness. Let me walk through what actually works.

Understanding Your Billing Table Access Patterns

Before touching indexes, you need to understand how your billing tables are accessed. In a typical self-storage billing system, the key tables are:

  • leases — queried by tenant, unit, status, and due date
  • lease_ledger — the highest-volume table; every charge, payment, and credit is a row here
  • payments — queried by date range, status, and gateway transaction ID for reconciliation
  • renewal_queue — queried by scheduled_date + status for the nightly run
  • invoices — queried by lease, date range, and status

Run SHOW TABLE STATUS and check the row counts. Then run SELECT * FROM information_schema.INDEX_STATISTICS WHERE TABLE_SCHEMA = 'your_db' to see which indexes are actually being used. Indexes that never get used are write overhead with no read benefit.

Indexing Strategies for Billing Tables

The ledger table deserves the most attention. Here's a schema that performs well at high volume:

CREATE TABLE lease_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lease_id BIGINT UNSIGNED NOT NULL,
    facility_id INT UNSIGNED NOT NULL,
    entry_type ENUM('charge','payment','credit','adjustment','reversal') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    entry_date DATE NOT NULL,
    posted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reference_id VARCHAR(64) NULL,
    category VARCHAR(32) NOT NULL DEFAULT 'rent',
    created_by INT UNSIGNED NULL,

    -- Composite index for per-lease balance calculation
    INDEX idx_lease_entries (lease_id, entry_type, amount),

    -- For date-range reports by facility
    INDEX idx_facility_date (facility_id, entry_date, entry_type),

    -- For reconciliation lookups by reference
    INDEX idx_reference (reference_id),

    -- For aging reports
    INDEX idx_entry_date_type (entry_date, entry_type)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

The idx_lease_entries index on (lease_id, entry_type, amount) is a covering index for the most common query in any billing system — calculating a lease's current balance. MySQL can satisfy this query entirely from the index without touching the clustered row data:

-- This query hits only the index, no row lookups
SELECT entry_type, SUM(amount)
FROM lease_ledger
WHERE lease_id = 12345
GROUP BY entry_type;

Run EXPLAIN and look for Using index in the Extra column. If you see Using index condition instead, the engine is doing index condition pushdown but still reading row data — you may need to adjust your column order in the composite index.

Reading EXPLAIN Output for Billing Queries

Here's an EXPLAIN for a common renewal queue processing query and how to interpret the output:

EXPLAIN SELECT id, lease_id
FROM renewal_queue
WHERE scheduled_date <= '2025-07-15'
  AND status = 'pending'
  AND (next_retry_at IS NULL OR next_retry_at <= NOW())
ORDER BY scheduled_date ASC
LIMIT 50;

What you want to see: type = range or better, key = idx_scheduled_status, rows estimate close to your actual result size, and no Using filesort in Extra. If you see Using filesort, the ORDER BY can't use the index and MySQL is sorting in a temp buffer — adding scheduled_date as the first column in your index and ensuring the WHERE clause uses it as a range predicate will usually fix this.

The columns in Extra to specifically watch for in billing queries:

  • Using where — filter applied after index lookup, usually acceptable
  • Using temporary on a GROUP BY — often indicates a missing composite index
  • Using filesort — sort can't use index; fix with index column order adjustment
  • Full scan on NULL key — NULL-safe comparison forcing a full scan; restructure the query

Table Partitioning for Ledger History

Once a ledger table crosses about 50 million rows, even well-indexed queries start showing degraded performance because the index B-trees themselves become large. Range partitioning by year (or quarter for very high-volume facilities) solves this by letting MySQL prune partitions before even consulting indexes:

ALTER TABLE lease_ledger
PARTITION BY RANGE (YEAR(entry_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

The key requirement for effective partition pruning is that your WHERE clause must include the partition key column (entry_date here) with a range predicate. A query like WHERE lease_id = 123 AND entry_date >= '2024-01-01' will prune to the 2024+ partitions. A query with only WHERE lease_id = 123 will scan all partitions — no benefit, slightly higher overhead. Partition pruning and query patterns must be designed together.

InnoDB Buffer Pool and Connection Pooling

For a dedicated billing database server, set the InnoDB buffer pool to 70–75% of available RAM. A server with 32GB RAM should have innodb_buffer_pool_size = 24G. This keeps your hot data — the current year's ledger rows, active leases, and renewal queue — in memory rather than hitting disk.

# /etc/mysql/mysql.conf.d/billing-tuning.cnf
[mysqld]
innodb_buffer_pool_size         = 24G
innodb_buffer_pool_instances    = 8
innodb_log_file_size            = 1G
innodb_flush_log_at_trx_commit  = 1     # 1 = full ACID; 2 = faster but fsync on crash
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000  # adjust for your SSD IOPS rating
innodb_read_io_threads          = 8
innodb_write_io_threads         = 8
max_connections                 = 200
thread_cache_size               = 50

For connection pooling, PHP billing applications typically use persistent connections via PDO or an external pooler like ProxySQL. If you're running a multi-server PHP setup processing renewals in parallel, ProxySQL gives you connection multiplexing so 20 PHP workers don't each hold their own dedicated MySQL connection. This matters because each MySQL connection consumes roughly 1MB of overhead, and max_connections = 200 with 20 PHP-FPM workers per server means you can realistically run 10 servers before hitting limits.

Query Optimization Patterns Specific to Billing

The most expensive recurring query pattern in billing systems is the "all tenants with outstanding balance" report. A naive implementation calculates balance per lease on-the-fly in the application layer. The better approach is a single SQL query with a derived table:

-- Find all active leases with positive balance, ordered by days overdue
SELECT
    l.id AS lease_id,
    l.tenant_id,
    l.unit_id,
    SUM(CASE WHEN ll.entry_type = 'charge' THEN ll.amount ELSE 0 END) -
    SUM(CASE WHEN ll.entry_type IN ('payment','credit') THEN ll.amount ELSE 0 END) AS balance,
    DATEDIFF(CURDATE(), l.current_period_due_date) AS days_overdue
FROM leases l
INNER JOIN lease_ledger ll ON ll.lease_id = l.id
WHERE l.status = 'active'
  AND l.facility_id = ?
GROUP BY l.id, l.tenant_id, l.unit_id, l.current_period_due_date
HAVING balance > 0
ORDER BY days_overdue DESC;

For this query, make sure you have a composite index on lease_ledger(lease_id, entry_type, amount) as described earlier, and an index on leases(facility_id, status, current_period_due_date). The HAVING clause filters after aggregation — there's no way to push that into an index — but the GROUP BY operand columns should all come from the index to avoid a filesort.

Database optimization for billing is iterative work. Add slow_query_log = 1 and long_query_time = 0.5 to catch queries over 500ms, run pt-query-digest weekly against the slow log, and treat the top-10 query patterns as a maintenance ticket. At high volume, the difference between a well-tuned billing database and a neglected one is measured in hours of nightly processing time.