I was wanting to create a personal note database, to store notes in HTML or text that are quite long. What would be the difference between VARCHAR and TEXT fields, which one would be more efficient to use?
I think VARCHAR's max is 65535 characters, I can't wrap my head around if I'll contain anything larger than that though. I wonder if a certain data type can be automatically compressed, although I don't think it's that simple (since it'd take too much CPU time in real use).
AFAIR TEXT is limited to 65k characters, MEDIUMTEXT is 16 millions, and LONGTEXT is 4GB max :-)
If you're not sure if the text will always be shorter than 65535 characters then do not risk to use varchar. I think that you definitely should use mediumtext or even longtext type but to increase performance place them into separate table. For example create table Notes that will contain note id, title, creation date and user that creates it and also NotesText table that will be associated with Notes but will contain only note id and your text column. That will prevent from querieng over text type columns and retrieving it from database when not really needed.
The VARCHAR maximum length isn't 65535 bytes; the maximum length of the row is that number of bytes, and the maximum length of the VARCHAR will depend on what other columns you have in the table. For that reason, if you are likely to need that many characters, you'll need a TEXT or BLOB column. These are held separately on the disk so tend to be slower (not in CPU usage but in total time).