MySQL: Most efficient data type to store long notes?

Question

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).

Answer 1

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.