Why does MySQL store blobs in the direct table rather than off to the side so that if it needs to read the corresponding stuff it will read the stuff off to the side? Essentially it would create its own file that was protected from everything except itself in a file-folder type of architecture so that it would be easier/quicker to write out a blob link.
Asked
Active
Viewed 141 times
1
-
3Is this a question? Or a proposal for the MySQL development team? – ypercubeᵀᴹ Aug 26 '14 at 20:18
-
I'm not sure if they already do MySQL that way, and if not why haven't they done it yet? How do I propose a revision along those lines? Does the MySQL team happen to be active on SE? – a coder Aug 26 '14 at 20:22
-
2Your suggestion is far from clear. What do you mean with "store off to the side"? What is this "file-folder" architecture you are suggesting? Tables are stored on disk, so essentially on (one or more) files. And what is a "blob link"? You probably need to come up with some clarifications about your suggestion. Why would it be easier or faster? MySQL team by the way, can be found from its site: dev.mysql.com. I'm pretty sure, SE is not the way to contact them. – ypercubeᵀᴹ Aug 26 '14 at 20:54
-
2Please don't close this. I have an answer I am writing up. Please stand by ... – RolandoMySQLDBA Aug 26 '14 at 21:22
-
@ypercube A hard drive is a database, and within that database it has all these tables, and within these tables it has small entries (file name, and extension), and big entries (blobs), why can't MySQL automatically just search for the small entries to grab the data necessary, then pull the big entry out when called specifically for it (like opening a file). A blob link would be the pointer to the blob itself instead of taking extra time to search the blob for the value, it would search for the pointer. – a coder Aug 26 '14 at 21:43
-
3@IGotRoot, how would your idea support transaction isolation, transaction rollback, redo logging, or crash recovery? You should learn more about what databases do, that makes a database not the same as a filesystem. – Bill Karwin Aug 27 '14 at 06:59
-
I know what databases do, they're like a file room. A blob file type could be a pointer to the actual blob that gets read if necessary through the key to a table that exists, but no one sees. – a coder Aug 27 '14 at 18:16
1 Answers
5
Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small.
I have discussed TOAST before in the DBA StackExchange
May 01, 2012: what is bigger than a longblob?Mar 21, 2012: Are many NULL columns harmful in mysql InnoDB?Jul 19, 2011: Different MySQL Datafile Sizes After Restoration
YOUR QUESTION
To answer your question, neither InnoDB nor MyISAM feature an external mechanism like TOAST. The closest thing for InnoDB is overflow pages (See MySQL Documentation).
SUGGESTION #1
You will have to tune for BLOBs in one or more ways:
- Create a table with a BLOB field and a foreign key to the parent table for the remainder of the data.
- In the event of multiple BLOBs in a single row, find a way to combine all the BLOB data into a single BLOB field and compress it and/or change the
ROW_FORMAT(See MySQLPerformanceBlog in this one). - Look for the biggest BLOB in your dataset and scale up the innodb_log_buffer_size and innodb_log_file_size to accommodate 10 such BLOBs (InnoDB only)
Aug 01, 2011: How does max_allowed_packet affect the backup and restore of a database?Apr 27, 2011: Changed max_allowed_packet and still receiving 'Packet Too Large' errorApr 20, 2011: MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?
SUGGESTION #2
Switch to PostgreSQL :-(
GIVE IT A TRY !!!
RolandoMySQLDBA
- 182,700
- 33
- 317
- 520