CODEWITHSUNDEEP
mysqlvb.netdatabase-designforeign-keysblob
Dadex
Dadex

Reputation: 129

Is it correct to have a BLOB field directly in the main table?

Which one is better: having a BLOB field in the same table or having a 1-TO-1 reference to it in another table? I'm making a MySQL database whose main table is called item(ID, Description). This table is consulted by a program I'm developing in VB.NET which offers the possibility to double-click a specific item obtained with a query. Once opened its dedicated form, I would like to show an image stored in the BLOB field, a sort of item preview. The problem is I don't know where is better to create this BLOB field.

Assuming to have a table like this: Item(ID, Description, BLOB), will the BLOB field affect the database performance on queries like: 

SELECT ID, Description FROM Item;

If yes, what do you think about this solution:

Item(ID, Description)
Images(Item, File)

Where Images.Item references to Item.ID, and File is the BLOB field.

Upvotes: 3

Views: 1289

Answers (3)

Rick James
Rick James

Reputation: 142218

Most of what has been said in the other Answers is mostly correct. I'll start from scratch, adding some caveats.

The two-table, 1-1, design is usually better for MyISAM, but not for InnoDB. The rest of my Answer applies only to InnoDB.

"Off-record" storage may happen to BLOB, TEXT, and 'large' VARCHAR and VARBINARY, almost equally.

"Large" columns are usually stored "off-record", thereby providing something very similar to your 1-1 design. However, by having InnoDB do the work usually leads to better performance.

The ROW_FORMAT and the size of the column makes a difference.

  • A "small" BLOB may be stored on-record. Pro: no need for the extra fetch when you include the blob in the SELECT list. Con: clutter.
  • Some ROW_FORMATs cut off at 767 bytes.
  • Some ROW_FORMATs store 20 bytes on-record; this is just a 'pointer'; the entire blob is off-record.
  • etc, etc.

Off-record is beneficial when you need to filter out a bunch of rows, then fetch only a few. Also, when you don't need the column.

As a side note, TINYTEXT is possibly useless. There are situations where the 'equivalent' VARCHAR(255) performs better.

Storing an image in the table (on- or off-record) is arguably unwise if that image will be used in an HTML page. HTML is quite happy to request the <img src=...> from your server or even some other server. In this case, a smallish VARCHAR containing a url is the 'correct' design.

Upvotes: 1

kiks73
kiks73

Reputation: 3758

You can add the BLOB field directly to your main table, as BLOB fields are not stored in-row and require a separate look-up to retrieve its contents. Your dependent table is needless.

BUT another and preferred way is to store on your database table only a pointer (path to the file on server) to your image file. In this way you can retrive the path and access the file from your VB.NET application.

Upvotes: 3

Mureinik
Mureinik

Reputation: 311103

To quote the documentation about blobs:

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.

In simpler terms, the blob's storage isn't stored inside the table's row, only a pointer is - which is pretty similar to what you're trying to achieve with the secondary table. To make a long story short - there's no need for another table, MySQL already doesn't the same thing internally.

Upvotes: 1

Related Questions