I want to use many text fields for a table but they can be very large so doing complex operations with rows of this table, like joins with big tables can become intractable or very slow
I understand that text field are not stored in memory so they forbid their rows from being cached, so I would like to switch off text field loading from disc making the rows of this table remain in fast memory and allowing load text fiels "manual loading" when necessary
Is this possible?
Maybe. That is, there is no setting to achieve such; however, rewriting the query can achieve such.
Please show us the query that is causing such. Meanwhile, here are some possible examples:
SELECT * ... loads all the text columns. If you don't need all the columns, spell out what you need instead of saying *.
SELECT big_text FROM t WHERE ... ORDER BY ... LIMIT 10 -- In some cases, the optimizer can focus on the 10 rows desired, and load big_text for only those. For other cases, it loads big_text for 'all' the rows before discovering which 10 you needed. This can usually be worked around. But I need your SQL to talk you through it.
JOIN is another case where the previous item may or may not show its ugly head.
Move the text columns to a separate table. This new table will have the same key as the original table. When a new row arrives write the small columns to the original table and the text columns to the new table with the same key value. This technique is called a vertical partition. If required there can be multiple new tables, each containing one or more of the text columns.
Use the original table to perform the complex query. Use this resultset to join to the text-containing tables only when required.
