Add more targeted indexes

I have made a special performance tuning database for everybody, who would like to help optimizing l10n server. This database is filled up with:

• All possible languages (179)
• Releases of all Drupal.org contributed modules version 5.x (around 1800 releases)
• Releases of all Drupal.org contributed modules version 6.x (around 3600 releases)
• All possible strings from releases above (around 123 000 strings)
• 5 000 000 of string translations, 1 for every string for every language. That makes it 179 * 123 000 = 22 000 000 translations, only 5 milion of them are generated, otherwise the interface would be slowing programmers from tuning the database.
• 350 000 of string suggestions

How to install
Because it's not easy to direct users how to install l10n server just to tune, I am providing a complete Drupal 5.11 tarball + database. Just untar, import your database and change settings.php to point to a new database. Administrator username is: admin, password is: admin

(Please note that due to my design error, all tables have 2_ prefix. But it's not a problem with Drupal API :)

What to expect
The User Interface for l10n_server is now fairly slow.

Download
Due to database dump size, I am providing download links here:

• Drupal 5.11 tarball: http://147.32.30.17/drupal-tuning.tar.gz
• Database dump: http://147.32.30.17/drupal-tuning-dump.sql.gz (58MB, 641MB gunzipped)

Enjoy!

OK, first to go:

this is used on "Translate" screen:
SELECT COUNT(*) FROM 2_l10n_community_translation WHERE is_suggestion = 0 AND is_active = 1;

and takes around 5 seconds on my laptop.

After adding an index, it drops to 3 seconds.

Attaching an EXPLAIN. Small step, but that counts! (We will have to cache this anyway).

This one would also use an index:
SELECT COUNT(DISTINCT uid_entered) FROM 2_l10n_community_translation GROUP BY uid_entered;

Attaching an output. From 7.52 secs to 2.96 secs.

Patch to go: #325721: Performance: introduce caching for Quick stats and Progress status

And the winner is...

# Time: 081024 22:33:56
# User@Host: root[root] @ localhost []
# Query_time: 531 Lock_time: 0 Rows_sent: 2930 Rows_examined: 125442574
SELECT COUNT(DISTINCT t.sid) translation_count, r.pid, t.is_suggestion FROM 2_l10n_community_release r INNER JOIN 2_l10n_community_file f ON r.rid = f.rid INNER JOIN 2_l10n_community_line l ON f.fid = l.fid INNER JOIN 2_l10n_community_string s ON l.sid = s.sid LEFT JOIN 2_l10n_community_translation t ON s.sid = t.sid WHERE t.is_active = 1 AND t.translation != '' GROUP BY r.pid, t.is_suggestion;

:-)

create index l10n_sid_language_is_suggestion ON 2_l10n_community_translation(sid,language,is_suggestion);

Will 2x speed up Edit & View language screen. Still - too slow :-)

Patch to go: #326423: Performance: add indexes to translations table