Jump to content

WHMCS database optimization

Recommended Posts

Googling around, I found this post about WHMCS's db optimization:  https://support.reliablesite.net/kb/a280/optimizing-the-whmcs-database-for-performance.aspx

that suggests following changes to improve performance:

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);

I'm always quite skeptikal about "simple and universal recipes for performance improvment" (would it be so simple and without any collateral effect, why hasn't it been implemented directly by the WHMCS development team?), and also a test done in my dev and test environment would have very little meaning ... so here I'm to ask your opinion and experience about this proposed "tricks", or other db optimizations that may be useful...

Share this post

Link to post
Share on other sites
Posted (edited)

Before making changes in the database, it should be confirmed if there are any slow queries at all or how long the queries take. This can be determined via the MySQL query log.

But even if there are slow queries, I would advise against making changes to the WHMCS database, this is certainly not recommended / supported by WHMCS. Instead, I would recommend optimizing the configuration of the MySQL server. Optimizing the MySQL server also has the most potential. I'm not assuming this, but if you're using the default configuration, there are definitely things to tweak (innodb_buffer_pool_size, innodb_buffer_pool_instances, innodb_read/write_io_threads, ...).

Edited by string

Share this post

Link to post
Share on other sites

Speaking in general, the more index you have the faster SELECT proform. Especially if such index are used for JOIN. And we all know that the above tables are super-connected with eachother. That being said, there is another side to the coin. The overuse of index slows UPDATE, INSERT and DELETE statements. Simply put, it's a competition between data retrieval and manipulation. You can't be fast at both* In other words with the above queries on paper you make "reads" faster but be prepared to wait a little longer when you update prices, configurable options etc.

* Actually you can. On tables with heavy data manipulation you ideally use only the primary index. This way updates, iserts and deletes are super fast. When it comes to retreiving data, every X minutes you export & sync data to a secondary table fully indexed. This way you can perform select, sort, join, group, having, where (...) in the most efficient way.


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated