Remove TEXT and BLOB from Broad tables as we know them.

[hanjoosten]

Problem

Attributes in tables, that have a large amount of space, like BLOB and TEXT, should be handled in separate tables, to enable better performance. In MySQL (and probably other database management systems as well), there is a penalty when joining tables that contain TEXT fields. These JOINs will create a temporary table on disk, not in memory. This impacts performance significantly.

Current situation

relations that are UNI are currently stored into the concept table of the source of the relation. Whenever the TType of the target of such a relation is represented in SQL as TEXT (or BLOB etc) then this causes the problematic effect.

Solution

We could take the TType of the target into account, when deciding to implement the relation in a concept table. Instead of putting it into the concept table of the source concept, we could store the relation into a separate BinSQL table, which would then be the lookup table for that relation. This will speedup all joins where that specific relation is not part of the expression, but any other part of the expression uses the concepttable it would otherwise be stored in.

[hanjoosten]

The nice thing is of course, that this would only impact the generation of plugs.

[Michiel-s]

I propose to close this issue. I haven't experienced any issues with this so far. We also don't have any statistics on the performance penalty. Furthermore, refactoring to a separate tabel for TEXT and BLOB is a large cost. More costly than increasing cpu or memory when needed.

We use ROW_FORMAT=DYNAMIC for our tables which optimized variable length columns already. Not sure what the effect is on JOIN performance

https://mariadb.com/kb/en/innodb-row-formats-overview/