Working and searching large amounts of text
From what I understand, VARCHAR columns are stored in the row and TEXT columns aren't. In reality how much does this affect performance? What are good practices for storing large amounts of text in MySQL. Specifically:
- If you're not selecting TEXT columns, will you receive any hit in performance?
- If you had a small string stored both in TEXT and LARGETEXT, do they take up the same amount of memory (i.e. they aren't like CHAR fields that are fixed in size)?
- On a FULLTEXT search, would having LARGETEXT when TEXT would do affect performance much? I'm assuming it won't since the query is on the index, not the column (unless you're selecting the column)
- On both LIKE and FULLTEXT search, is it better to have NULL over an empty string for rows with nothing to search?