I have a solution for this query, I will just check the rest to propose optimizations on the whole project as it's mainly a problem of indexes not being set properly.
The homepage is built in L10nCommunityWelcomePageController::build(), which at some point calls L10nStatistics::getProjectsStringCount(), where the following SQL query is run in about 9 seconds on my PC (knowing that only a subset of l.d.o data is loaded as far as I know):
SELECT COUNT(DISTINCT sid) AS string_count, pid FROM l10n_server_line GROUP BY pid;
This page suggests that it could be rewritten this way in order to force InnoDB to plan the query differently (i hope it yields the same result):
SELECT COUNT(i.sid) AS string_count, i.pid FROM (SELECT DISTINCT sid, pid FROM l10n_server_line) i GROUP BY pid;
This one "only" takes about 3 seconds to complete.
Another way would be to select a different engine for this table (MyISAM), but I have little idea of the implications and absolutely no experience. What do you think?
Anyway, we will also have to choose a caching strategy in the controller (see line 236): max age ?
Active
3.0
Code
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
I have a solution for this query, I will just check the rest to propose optimizations on the whole project as it's mainly a problem of indexes not being set properly.