CODEWITHSUNDEEP
mysql
István Kohn
István Kohn

Reputation: 135

How to store pictures effectively in a database?

I'm planning to store pictures in a database (MySQL 5.6.15, InnoDB) as MEDIUMBLOB and I'd like to know which table structure is more effective.

For every picture I'd store a checksum, and every time the checksum is checked first, to see if the picture needs updating. Considering the checksum will be accessed every time, but the picture may not be, does it make a difference (in performance) if I store them in a single table or two seperatre ones:

| user_id | picture | picture_checksum |

or

| user_id | picture |

| user_id | picture_checksum |

(P.S.: I don't want to get into the "file system vs database" debate, it's pretty much settled i'll be using a database.)

Upvotes: 0

Views: 383

Answers (1)

tadman
tadman

Reputation: 211540

A single table is the way to go here as the data is intrinsically related and you don't have an X-to-many relationship.

Be careful not to load the image when doing simple queries. SELECT * is a bad idea when it results in slamming megabytes of data over your connection if you never use that data.

Instead, be as precise as possible:

SELECT picture_checksum FROM pictures WHERE user_id=?

As a note, you'll probably want to use something more robust and collision resistant like SHA1 than a simple checksum.

Upvotes: 1

Related Questions