This is a long standing issue that hits us harder and harder as the time goes.
To summarise: When AMOS was designed in 2010, the DB schema was designed in a way that for every Moodle branch (2.0, 2.1, 2.2, ...) all strings would be effectively copied to reflect the branches in the SCM (where also strings can have different texts on different versions, be deleted etc).
It soon became obvious that we are wasting a lot of DB space by storing the same string values. So
MDLSITE-2670 became with some normalisation and each distinct text value could be stored just once, reducing the DB size.
However, a lot of conceptual problems remained. Primarily that the design required to manually clone branch X to X+1 on every release, the overall size of the amos_repository table - impacting the performance of searches etc. Additionally, this makes generating ZIP packs very long process because we have to start building from 2.0 and gradually add etc.
So what we really need is a next step that would:
- Change the DB scheme so that instead of associating string texts with a particular branch, we will store strings like valid since certain version up, unless said otherwise. Given that most strings never change in their life time, this will significantly reduce the amount of records.
- Split English strings and their translations into two tables. Again to have the number of rows and thence indexes and everything, better balanced.
This requires significant rewrites of many queries and associated code. But it is a good opportunity to do it now as we are making AMOS compatible with 3.10 branch.