I have just had a quick scan of the code, I don't think there is anything broken as such, just very cumbersome code for what needs to be done and sometimes a roundabout way of doing it.
Firstly you don't want to be performing the following on shared hosting for every user that connects.
The math should be done in PHP to take some of the load on the SQL Server.
Also there is 12 calls to the SQL Server in the index.php file alone. let alone any external functions that are called from core.php.
I think the best fix would be to change software or upgrade your hardware.
Firstly you don't want to be performing the following on shared hosting for every user that connects.
Code:
$sql = "SELECT COUNT(*) AS num_addresses, MAX(balance) AS max_balance, SUM(balance) as sum_balance, ";
$sql .= "MAX(totalbalance) as max_totalbalance, SUM(totalbalance) as sum_totalbalance ";
$sql .= "FROM balances WHERE email <> 'SERVERBALANCE'";
$sql .= "MAX(totalbalance) as max_totalbalance, SUM(totalbalance) as sum_totalbalance ";
$sql .= "FROM balances WHERE email <> 'SERVERBALANCE'";
The math should be done in PHP to take some of the load on the SQL Server.
Also there is 12 calls to the SQL Server in the index.php file alone. let alone any external functions that are called from core.php.
I think the best fix would be to change software or upgrade your hardware.
Do you even SQL bro
? I'll explain what this SQL statement is doing and why you're not correct in saying that this should be done PHP side. This SQL statement is getting1. The count of all rows in balances (so the amount of users).
2. The maximum balance of any user (so the person with the most $$). To do this one PHP sided, you'd have to retrieve the whole balance row from the table with all of the data and then do it PHP sided. This would use more CPU than the SQL statement would.
3. The sum of the balances is just all of the balances in the table added together. This is the same as above and doing it using MySQL is far less resource intensive then getting the whole table and doing it with PHP.
Your statement about "12 calls to the SQL Server in the index.php" isn't necessarily an issue. You'll find that a lot of PHP files have quite a few more queries.
@OP: Try get information such as what pages are causing this issue with overusage of CPU from your host. I can try later on a VPS and see what the major issue is for you if you want. Are there any cron scripts running by any chance (haven't really looked at the code).