Can there beOrhas somebody any comparison, personal expertise, or guideline when you should make use of the
text type rather than a sizable
varchar in MySQL?
While the majority of the records during my database is going to be under 1000 figures, some might require 4000 figures or even more. What's the restricting period of
text a much better variant?
I don't need to index individuals fields.
I do not have personal expertise, but this person does:
Quick answer: varchar would be a good bit faster.
Edit - no, it had not been. He was indexing them in a different way - he'd a complete index around the varchar (255 chars) but a 255-char prefix index around the text. As he removed that, they carried out pretty much exactly the same.
Later within the thread is interesting bit:
Whenever a tmp table is required for any Choose, the very first choice is by using MEMORY, which is RAM-only, hence most likely noticeably faster. (Second option is MyISAM.) However, TEXT and BLOB aren't permitted in MEMORY, therefore it can't utilize it. (You will find some other reasons why it could skip MEMORY.)
Edit 2 - more relevant info, this time around evaluating the way in which different indices cope with the different types.
MyISAM puts TEXT and BLOB 'inline'. If you're searching a table (range scan / table scan), you're 'stepping over individuals cow paddies' -- pricey for disk I/O. That's, the presence of the inline blob affects performance within this situation.
InnoDB puts only 767 bytes of the TEXT or BLOB inline, the relaxation adopts another block. This can be a compromise that sometimes helps, sometimes affects performance.
Another thing (Maria? Falcon? InnoDB wordpress plugin?) puts TEXTs and BLOBs entirely elsewhere. This could create a noticeable difference in performance when in comparison to VARCHAR. Sometimes TEXT could be faster (eg, range scan that doesn't require the blob) sometimes the VARCHAR could be faster (eg, if you want to view it and/or give it back).
Obviously the easiest method to know would be to run some tests yourself together with your real dataset, or at best a simulated equivalent. Just write some scripts to populate the information and run your chooses. Test with varchar at different dimensions, then text, and measure both timing and overall system utilization (cpu/load, memory, disk i/o).
If you are planning to possess enough load this will matter then you need to have automated tests anyway.