WHMCS database optimization

[Remitur]

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...

[string]

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 January 8, 2021 by string

[Kian]

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.