Where does performance come from

First of all, the machine has to be good. You may be dealing with millions of rows in a table with indexes. Don't install it on your laptop.

Here's a comparison between two machines with the same database (+4 million rows in table):

A virtual machine: https://l10n.mozilla.org/narro/translate.php?l=vi&p=&f=&t=1&s=&o=&h=1&m=10&i=0#i

First page load: 9 seconds
Average load when scrolling down: 10 seconds

Cheap shared hosting: http://narro.alexxed.com/lmo2/translate.php?l=vi&p=28&f=&t=1&s=&o=&h=1&m=10&i=0

First page load: 3 seconds
Average load when scrolling down: 2 seconds

If you're thinking of having multiple active languages and huge translation projects, it might be best to have separate installations per language.

What I can still try and will try to make it better is:

• have a table for each language rather than all languages in a table
• play with indexes and see what works best
  
• try PostgreSQL

Query ran for the tests above:

SELECT
*
FROM `narro_context_info` AS `t0`
LEFT JOIN `narro_context` AS `t1` ON `t0`.`context_id` = `t1`.`context_id` 
LEFT JOIN `narro_file` AS `t2` ON `t1`.`file_id` = `t2`.`file_id` 
LEFT JOIN `narro_text` AS `t3` ON `t1`.`text_id` = `t3`.`text_id` 
LEFT JOIN `narro_project` AS `t4` ON `t1`.`project_id` = `t4`.`project_id` 
LEFT JOIN `narro_suggestion` AS `t5` ON `t0`.`valid_suggestion_id` = `t5`.`suggestion_id`
WHERE (
   (
   `t0`.`language_id` = 60 AND 
   `t1`.`active` != 0 AND
    `t2`.`active` != 0
    ) AND 
    `t1`.`project_id` = 28 AND 
    `t0`.`has_suggestions` = 0
) 
LIMIT 20

Been there, done that ? Your comments would help.