1

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.

  • 3
    Is 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
  • 2
    Your 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
  • 2
    Please don't close this. I have an answer I am writing up. Please stand by ... – RolandoMySQLDBA Aug 26 '14 at 21:22
  • 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 6:59
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

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:

SUGGESTION #2

Switch to PostgreSQL :-(

GIVE IT A TRY !!!

  • 1
    GIVE IT A TRY!!! ... there, now your answer is perfect. – Kermit Aug 27 '14 at 0:05
  • 2
    Suggestion #2 FTW!!!! – swasheck Aug 27 '14 at 0:13
  • 4
    Why a :-( for #2? That should be a :-)! – a_horse_with_no_name Aug 27 '14 at 5:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.