Optimizing the WHMCS Database for Performance

Out of the box WHMCS has major database design issues that cause performance issues. Please use the following SQL statements within your MySQL database to increase performance on specific queries.
IMPORTANT: Be sure to backup your database prior to making these changes. We take no liability for any issues caused.
ALTER TABLE tblproductconfigoptionssub DROP INDEX configid, ADD INDEX configid (configid ASC, hidden ASC, sortorder ASC, id ASC);
ALTER TABLE tblpricing ADD INDEX relid (relid ASC, type ASC, currency ASC);
ALTER TABLE tblproductconfiglinks ADD UNIQUE INDEX pid_gid (pid ASC, gid ASC);
ALTER TABLE tblproductconfigoptions DROP INDEX productid, ADD INDEX productid (gid ASC, hidden ASC);

Add Feedback