I need to save some data in MySql which required to be retrieved super fast. Since there is a limit for the text amount which we can insert in MySql i break it into parts and add a sequence number to them. So when i fetch them i fetch them all and merge. Is this approach is faster than blob?
Asked
Active
Viewed 208 times
1
-
Are the text longer than LONGTEXT? 4gb? http://stackoverflow.com/questions/13932750/tinytext-text-mediumtext-and-longtext-in-mysql-maximum-storage-sizes – Mad Dog Tannen Jan 20 '15 at 14:08
-
No. it should not even pass 10MB. So the max limit would be 10MB. So do you think MEDIUMTEXT woudl do better than handling small text in multil rows ? – dinesh707 Jan 20 '15 at 14:39
-
1Yes, anyday, all the time. – Mad Dog Tannen Jan 20 '15 at 14:50
-
1When using any of the TEXT columns, you can also create a fulltext index on them for fast searches, if that is needed. With blobs you cannot. – Mad Dog Tannen Jan 20 '15 at 14:52
1 Answers
0
If your MEDIUMTEXT/MEDIUMBLOB is compressible, then compress it in the client and store it into a MEDIUMBLOB. Ordinary text is 3x compressible. Most image formats are not compressible.
Why? By compressing in the client, you are transferring only 3MB, not 10MB to/from the server. Storing 3MB on disk is a lot faster than 10MB.
Compression will disallow FULLTEXT.
If you need to store more than 16MB, your chunking may be the 'right' solution -- for a variety of reasons we have not yet touched on.
Another approach is to store the 10MB in a file, then store the filename (or URL, or whatever) in the database in a VARCHAR. There are pros and cons to this; it is a common question.
Rick James
- 135,179
- 13
- 127
- 222