A few things worth checking and trying, roughly in order of priority:
First, if you haven't already, turn on the MySQL slow query log (slow_query_log = 1, long_query_time = 2) so you can see the exact queries rather than just symptoms. Once you've got the actual queries, run EXPLAIN on each one — you're looking for type: ALL (means it's doing a full table scan), a high number of rows examined relative to what's actually needed, and Using filesort or Using temporary in the Extra column, which usually means your GROUP BY / ORDER BY isn't backed by an index.
My guess is indexing is going to be the biggest single win here. Tournament stats queries tend to filter and sort on the same columns every time (tournament_id, player_id, status, score), so if those aren't indexed — especially as composite indexes matching the actual query pattern, like (tournament_id, score DESC) for leaderboard sorts — MySQL is scanning way more rows than it needs to. Worth checking whether these tables have any indexes beyond the primary key at all.
While you're in there, it's also worth checking the storage engine. If any of these tables are still MyISAM instead of InnoDB, that alone could explain the concurrency slowdown you're describing — MyISAM locks the whole table on writes, so if leaderboard reads and score updates are hitting the same table at the same time during a busy tournament, everyone queues up behind each other. That fits the "only slow when multiple tournaments are running concurrently" pattern really well.
On the query side itself, a couple of common culprits: COUNT(DISTINCT ...) getting used unnecessarily after a join (often the join duplicates rows and a plain COUNT would double up, so people reach for DISTINCT when really the query structure is the issue), and per-row subqueries used to calculate rankings — those effectively turn into N extra queries per page load. If you're on MySQL 8, window functions like RANK() OVER (PARTITION BY tournament_id ORDER BY score DESC) can replace that in a single pass.
But honestly, the real fix for "many people looking at leaderboards during concurrent tournaments" is caching — recalculating the same leaderboard from scratch on every single page view is the actual scaling bottleneck, not just query efficiency. A few options that work on shared hosting:
- Simplest: a small leaderboard cache table that gets updated whenever a score changes, and pages read from that directly instead of running the full join/aggregate every time.
- If APCu is enabled on the hosting (common on cPanel), cache the computed leaderboard per tournament for even 15-30 seconds. That alone would collapse a huge number of repeated identical queries during peak viewing.
- If APCu isn't available, a simple file-based cache with a timestamp check works almost as well.
One more small thing: if a single page is running the same stats query more than once (pretty common in Core PHP apps that weren't built with a shared data layer), just storing the result in a PHP variable for that request avoids hitting the DB twice for the same data.
If I had to guess where to start: confirm InnoDB, add the missing composite indexes based on what EXPLAIN shows, then drop in a leaderboard cache table or APCu layer. Between those three, I'd expect the 12-20 second queries to drop to well under a second without needing any hosting/infra changes.
Note: I don't have experience in php, but i assessed this issue with multiple AIs and thought this to be sensible option worth trying.