Performance: denormalize releases, files and lines with project/release info

Just saving progress. Attached patch adds new columns and copies data from existing installations.

Working on inserting data during data creation and selects.

OK, this is actually getting ready for review, Gabor :-)

Status: New columns in database, update is moving data to them, extractor is able to save strings and files with the data.

Moving to simplifying the join query

OK, this seems to be done. Implemented stuff:

- Adding new fields
- Populating them with data either on update from old installation and both fresh installation and scanning packages
- Editing screen using new columns

Moving to next issue.

See:
SELECT COUNT(DISTINCT t.sid) AS translation_count, r.pid, t.is_suggestion FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid WHERE t.is_active = 1 AND t.translation != ''

Changed into:

SELECT COUNT(DISTINCT t.sid) AS translation_count, l.pid, t.is_suggestion FROM {l10n_community_translation} t LEFT JOIN {l10n_community_string} ON t.sid = s.sid INNER JOIN {l10n_community_line} l ON t.sid = l.sid WHERE t.is_active = 1 AND t.translation != '' 

Is there a reason to join from translation to string and then to line if we already have sid in translation so joining translation+line would be enough?

Sorry for misunderstanding...

See:

SELECT COUNT(DISTINCT t.sid) AS translation_count, l.pid, t.is_suggestion FROM {l10n_community_translation} t LEFT JOIN {l10n_community_string} s ON t.sid = s.sid INNER JOIN {l10n_community_line} l ON t.sid = l.sid WHERE t.is_active = 1 AND t.translation != '' 

Compared to:

SELECT COUNT(DISTINCT t.sid) AS translation_count, l.pid, t.is_suggestion FROM {l10n_community_translation} t INNER JOIN {l10n_community_line} l ON t.sid = l.sid WHERE t.is_active = 1 AND t.translation != '' 

This should give same results in my opinion but I found the previous version (with older joins too) in the code so I am trying to find a reason for that :)

Attaching new patch.

How to test revised SQL queries:

- Go to Explore projects page, Explore languages page and Translate welcome screen
- Note all numbers displayed on each page
- Apply patch
- DELETE FROM cache;
- Reload all pages

All numbers should stay the same

I would also like to do some benchmarks.

First impressions:

mysql> explain SELECT COUNT(DISTINCT(s.sid)) FROM l10n_community_release r INNER JOIN l10n_community_file f ON r.rid = f.rid INNER JOIN l10n_community_line l ON f.fid = l.fid INNER JOIN l10n_community_string s ON l.sid = s.sid LEFT JOIN l10n_community_translation t ON s.sid = t.sid AND t.language = 'ab' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE r.pid = 2670;
+----+-------------+-------+--------+--------------------------------------------------------------------------------+-------------------------+---------+----------------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                                                  | key                     | key_len | ref                                    | rows | Extra       |
+----+-------------+-------+--------+--------------------------------------------------------------------------------+-------------------------+---------+----------------------------------------+------+-------------+
|  1 | SIMPLE      | r     | ref    | PRIMARY,pid                                                                    | pid                     | 5       | const                                  |  106 | Using where | 
|  1 | SIMPLE      | f     | ref    | PRIMARY,rid                                                                    | rid                     | 5       | l10nserverdenormfull.r.rid             |    7 | Using where | 
|  1 | SIMPLE      | l     | ref    | fid,sid                                                                        | fid                     | 5       | l10nserverdenormfull.f.fid             |   16 | Using where | 
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                                                                        | PRIMARY                 | 4       | l10nserverdenormfull.l.sid             |    1 | Using index | 
|  1 | SIMPLE      | t     | ref    | is_active,is_suggestion,language,suggestion_active,sid_language_suggestion,sid | sid_language_suggestion | 46      | l10nserverdenormfull.s.sid,const,const |    1 |             | 
+----+-------------+-------+--------+--------------------------------------------------------------------------------+-------------------------+---------+----------------------------------------+------+-------------+
5 rows in set (0.00 sec)

New query:

mysql> explain SELECT COUNT(DISTINCT(s.sid)) FROM l10n_community_line l INNER JOIN l10n_community_string s ON l.sid = s.sid LEFT JOIN l10n_community_translation t ON s.sid = t.sid AND t.language = 'ab' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE l.pid = 2670;+----+-------------+-------+-------+--------------------------------------------------------------------------------+-------------------------+---------+----------------------------------------+--------+-------------+
| id | select_type | table | type  | possible_keys                                                                  | key                     | key_len | ref                                    | rows   | Extra       |
+----+-------------+-------+-------+--------------------------------------------------------------------------------+-------------------------+---------+----------------------------------------+--------+-------------+
|  1 | SIMPLE      | s     | index | PRIMARY                                                                        | PRIMARY                 | 4       | NULL                                   | 117996 | Using index | 
|  1 | SIMPLE      | t     | ref   | is_active,is_suggestion,language,suggestion_active,sid_language_suggestion,sid | sid_language_suggestion | 46      | l10nserverdenormfull.s.sid,const,const |      1 |             | 
|  1 | SIMPLE      | l     | ref   | sid                                                                            | sid                     | 5       | l10nserverdenormfull.s.sid             |      2 | Using where | 
+----+-------------+-------+-------+--------------------------------------------------------------------------------+-------------------------+---------+----------------------------------------+--------+-------------+
3 rows in set (0.00 sec)

BUt second (new) seems to be much slower!

The reason why the second query is slower is likely that it looks at much more rows in the first table.

Maybe a better index helps.

Actually, the best way to deal with this might be the materialized views approach that David has taken in tracker2, ie we'd keep a table with precalculated values.

Well, should I try to implement something like materialized views or should we continue on this patch?

Changing the query to LEFT JOIN saved over 2 seconds...

Posting an updated patch.

Forgot to delete from releases, fixed one phpdoc