Mysql – Can text fields be not loaded for join memory purposes in MySQL

join;MySQL

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?

Best Answer

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.

Related Question