CODEWITHSUNDEEP
mysqldatabasedatabase-design
etzzz
etzzz

Reputation: 165

Store Blob in another table or use mysql barracuda

In order to speed up reading process, i thinking of transferring blob from one table to another table. But then i saw other people from stack overflow recommended to use MySQL barracuda as this method eliminate the need of transferring the blob.

My database (Design by old programmer)

Visitor_visit (table name) -> contain 5 blob column. Thought of moving the blob to another table.

So which method should i use? And how should i separate the table? Do i need to create 5 tables for each of the blob for better performance? I not really a MYSQL expect.

I wouldn't prefer to store image in the disk now. (Current situation would not let me do so)

Upvotes: 0

Views: 83

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

Barracuda doesn't change how blobs are stored as much as you might think. If the blob fits on a data page with the row of other columns, it will stay together with those columns. Only if the blob is larger than what will fit on the page, is it stored off-page.

Compare with Antelope, where a large blob will also be stored mostly off-page, but the first 768 bytes of the blob will be stored on the main page with the other columns. So we're just talking about a difference in the way the blob's first 3/4 of a kilobyte is stored.

In both cases, if you simply don't reference the blob column in your query (and you shouldn't use SELECT * for this reason), InnoDB is smart enough to avoid fetching extra blob pages it doesn't need. You don't have to make another table to store blobs.

Upvotes: 1

Rick James
Rick James

Reputation: 142218

Let's see the table schema (SHOW CREATE TABLE), some clues of the sizes of the blobs, etc.

Normally, you should not bother doing anything about the 5 blobs. Everything should be automatic.

However, there are some aggregate size limitations. Again, please provide more specifics if you have hit some error message.

Meanwhile, I see no inherent reason to do "vertical partitioning".

Upvotes: 0

Related Questions