# WHMCS Database Optimization

> Lean, faster WHMCS databases: log pruning you approve, indexes on addon tables, InnoDB, collation and my.cnf fixes.

MySQL and MariaDB work for large WHMCS installs: table size report, slow-query review, agreed log pruning, InnoDB and utf8_unicode_ci fixes and my.cnf tuning.

- **Price:** Starting at $199
- **Billing:** One-time, fixed scope
- **Category:** Server Management
- **Quote turnaround:** Fixed-price quote within 24 hours
- **Start a project:** https://whmcspilot.com/start-project
- **Details:** https://whmcspilot.com/services/whmcs-database-optimization
- **Last updated:** 2026-11-05

## Overview

WHMCS database optimization deals with the MySQL or MariaDB side of an install that has grown for years: log tables bigger than the billing data, slow queries from addons and reports, mixed storage engines and collations, and server settings nobody changed from the defaults. One WHMCS database starts at $199. When the slowness is in PHP or the cron, [performance tuning](/services/whmcs-performance-tuning) is the better match, and heavy pages in the browser belong to [speed optimization](/services/whmcs-speed-optimization).

## Where WHMCS databases put on weight

- **Activity Log.** WHMCS trims system entries to the activity log limit you set, but client entries stay until someone prunes them. On older installs tblactivitylog is often the largest table.
- **Email Message Log.** A copy of every client email, stored in tblemails, for as long as the install has existed.
- **Debug logging left on.** Hooks Debug Mode, SQL Debug Mode or Module Logging, switched on for an old support case and never switched off. WHMCS warns that they make the database grow very quickly.
- **Gateway, WHOIS and ticket import logs.** Rows nobody reads after a few months.
- **Addon tables.** Third-party addons that log every API call, often with no index on the columns their own pages search.

## What the job includes

### 1. A size and query report

We list every table by data size, index size and row count, then run MySQL's slow query log during a normal business day. The worst queries are checked with EXPLAIN to see whether they scan whole tables. Most turn out to come from a custom report, a hook or an addon rather than WHMCS core.

### 2. Indexes where they are safe

Missing indexes are added to addon and custom tables. We avoid changing the structure of core WHMCS tables, because the updater expects its own schema; where a core query is slow, we fix the hook or report that triggers it.

### 3. Pruning you agree to first

Before anything is deleted, you decide what stays for audits, disputes and tax records. Invoices, transactions and client accounts are never pruned. Where the Admin Area has a tool for it, we use it: Prune Client Activity Logs, Prune Saved Emails and Prune Old Attachments in the Advanced tab of Utilities &gt; System &gt; System Cleanup, and the Empty options for the gateway and WHOIS logs. Older rows can be exported to a compressed archive file first if you want to keep them outside the live database. The Prune Ticket Attachments automation setting keeps attachments from piling up again.

### 4. Engine, collation and strict mode

Leftover MyISAM tables from old installs lock the whole table on every write, so we convert them to InnoDB. Tables are brought to the utf8\_unicode\_ci collation WHMCS recommends, since mixed collations can break queries and leave the admin dashboard blank. Strict SQL modes, which WHMCS does not support, are taken out of sql\_mode.

### 5. Server settings

In my.cnf we size innodb\_buffer\_pool\_size to your data and RAM, set max\_connections to what the server can actually serve, and meet the minimums System Health checks for: connect\_timeout of 10, wait\_timeout and interactive\_timeout of 300, and a max\_allowed\_packet of at least 4 MB (WHMCS's performance notes suggest 16 MB). After large deletions we run OPTIMIZE TABLE, which is what the Optimise Tables button at Utilities &gt; System &gt; Database Status does, in a quiet window so locks do not affect customers.

## How we keep your data safe

- A full dump before any change, checked by loading it into a scratch database
- Deletions in small batches, so no single statement holds a long lock
- The cron paused during the largest operations, then checked once it resumes

## What we need from you

- Database access through SSH or phpMyAdmin, and a WHMCS admin login
- Temporary ALTER, CREATE, DROP and INDEX privileges if you removed them from the WHMCS database user, as WHMCS allows for daily operation
- Your retention rules for logs and saved emails

## What you get back

1. Before-and-after table sizes and query times.
2. Every index added and every setting changed, including the my.cnf differences.
3. A short schedule for keeping logs in check, including which debug settings must stay off.

## Pricing

ScopePriceSize report, pruning, engine and collation fixes and my.cnf review for one databaseFrom $199Slow queries inside custom addons or reports that need code changesFixed quoteA dedicated database server, replication or very large databasesFixed quoteLater changes$65/hour

Your WHMCS version, 8.13 or 9.x, does not change this work.

## Not covered

- Switching on Automatically Delete Inactive Clients, which permanently removes client records and is a decision for you and your accountant
- Reporting dashboards built on the data, which is [reporting and analytics](/services/whmcs-reporting-analytics)
- Backup design; if dumps are slow or untested, add [backup and disaster recovery](/services/whmcs-backup-setup)

A screenshot of the table list at Utilities &gt; System &gt; Database Status is usually enough for a first estimate, so include one when you write.

## What's included

- Table size, index size and row count report
- Slow query log and EXPLAIN review of the worst queries
- Indexes added to addon and custom tables
- Activity, email, module, gateway and WHOIS logs pruned to rules you approve
- Debug logging settings found and switched off
- MyISAM tables converted to InnoDB
- Collation aligned to utf8_unicode_ci and strict mode removed
- my.cnf buffer, connection and timeout settings reviewed
- OPTIMIZE TABLE run in a quiet window after large deletions
- Verified full dump taken before any change

## Business outcomes

- Admin searches and reports stop waiting on oversized tables
- Backups get smaller and finish sooner
- Records you need for audits and tax are kept, by your rules
- System Health stops flagging MySQL configuration warnings
- The database stays lean afterwards because the causes are switched off

## Frequently asked questions

### Which WHMCS tables usually grow the most?

The Activity Log and the Email Message Log on most installs, and the Module Log wherever module debug logging was left on. Addon tables that log API calls come next.

### Will you delete invoices or client accounts?

No. Pruning covers logs, saved email copies and old ticket attachments only, and you approve the cutoff dates first.

### Do you add indexes to WHMCS core tables?

We avoid it, because WHMCS updates expect their own schema. Indexes go on addon and custom tables, and slow core queries are fixed at the hook or report that calls them.

### Does WHMCS work with MariaDB?

WHMCS is built for MySQL and says binary-compatible alternatives such as MariaDB may also work. We tune whichever your server runs.

### How much does it cost?

From $199 for one database, covering the report, pruning, engine and collation fixes and server settings. Code changes in addons and very large databases are quoted after the call.

### What payment options are there?

The deposit is 50%, with the other 50% due when the report and changes are handed over. We accept PayPal, crypto through NOWPayments, and card or wallet payments processed by Stripe.
