Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Although related to this question, I have a slightly different angle to approach this issue from. Here is my situation:

I am writing a web application (either in PHP or Python) that manages addons for a desktop application. Users can browse addons, install them, upload them, etc.

I am planning the schema for the database and found myself with a decision to make:

Is it better to store icons (for the addons) in the table itself or instead store them in the filesystem and simply store a filename in the table?

The icons are small (48x48 or close to that) and likely won't take up more than 5 or 6 KB at the most. Are there any serious drawbacks to storing the image data in the table? Are there other implications that I should be aware of? Will performance be a concern? Will storage be an issue?


Edit: I am currently looking at MyISAM tables in a MySQL database.

share|improve this question
1  
Though not exact duplicates of your question, you might find useful information in the following questions: Is it better to store images in a BLOB or just the URL? and Files - in the database or not?. With small pics I'd say you're safe with keeping files in the db, though you'd need to plan carefully for future storage. What's the database platform? Maybe there are specifics for it. –  Marian Aug 21 '11 at 21:32
2  
I feel this question has been answered again and again myself @Marian so ... I wonder if it's time to start closing as dupe or if we can eke something new out of this discussion. –  jcolebrand Aug 21 '11 at 23:36
    
@Marian: MyISAM tables on a MySQL database at this point. –  George Edison Aug 22 '11 at 3:31
    
@JColebrand - as long as the question doesn't bring anything new, I suppose it's better for George to redirect him to those questions. George - have you taken a look on those questions? Is there anything that's not treated there? –  Marian Aug 22 '11 at 8:28
    
@Marian: Not really - feel free to merge / close this one if need be. –  George Edison Aug 22 '11 at 17:48
add comment

2 Answers

up vote 2 down vote accepted

You haven't specified the database platform your considering but at this size/scale, it's unlikely to matter.

5kb per record is trivial. 1 million 5kb records is < 5GB, still trivial. 10 million 5kb records... still not something to lose any sleep about.

If we were to get platform specific, a typically exhaustively researched white paper by Paul Randall on SQL Server filestream storage suggests that it outperforms table storage where files are 1MB or greater in size. Below 1MB file sizes, the positives are primarily around filestream bypassing the buffer pool.

The positives for database storage:

  1. Transacted. The binary data is tied to it's metadata, not exposed by a filesystem, safe from accidental deletion.
  2. Reduced backup overhead. 1 million records in a 5GB file vs 1 million 5kb files.
  3. The natural companion to 2), faster restore.

Edit: The negatives (as suggested by Aaron)

  1. Requires programatic access.
  2. Cost of storage. More of an issue if you have to deal with platform restrictions on size e.g. 50GB on Azure.
share|improve this answer
2  
One is programmatic access. If you're trying to troubleshoot, you can't just say SELECT img FROM tbl; Another is the cost of storage. At a web host, for example, the cost of database storage is much higher than file storage. It's also less flexible; I can move my imgs from azure to amazon easily. Transacted isn't necessarily a benefit because that storage also means larger backups. In this specific case these issues aren't huge (no pun intended) but I don't think they should be ignored. That all said, I agree with the comments above - this discussion has been beaten to death over and over... –  Aaron Bertrand Aug 22 '11 at 2:22
    
Sorry - I totally forgot to mention specifics about the database. I've updated the question now. –  George Edison Aug 22 '11 at 3:32
    
Good points Aaron, edited in. Not sure on your point regarding backup however, are you considering database backup costs as higher than file? –  Mark Storey-Smith Aug 22 '11 at 7:12
add comment

Another interesting aspect that makes storing image data rather challenging is having the correct packet size. I addressed this back on April 27, 2011.

Since images are to be stored in BLOB fields, there will be internal operations and/or external communications of BLOB data via programs (such as mysqldump), infrastructure (such as MySQL Replication), and general query usage (such as having BLOB data in internal temp tables during JOINs and WHERE clause evaultion).

In addition, as mentioned in previous URL, the InnoDB storage engine has a way of handling packets in memory and logs files.

Not be be overlooked is to construct SQL queries that

  • avoid performing any WHERE clauses against the BLOB data
  • keep BLOB data out of as many temp tables as possible (may call for refactoring queries)

In light of these facts, you will have to configure my.cnf with a number for max_allowed_packet so that a single MySQL packet is large enough to accommodate multiple BLOBs. You must also look into having enough available RAM on the DB Server. Otherwise, handling BLOBs in bulk with constantly moving a single BLOB in and out of a packet will produce unexpected performance bottlenecks.

CONCLUSION

Most of the Considerations/Drawbacks should cancel each other out once you have enough available RAM, properly-sized MySQL packets, and queries that avoiding piling up BLOB data in multiple temp tables.

UPDATE 2011-09-09 12:23 EDT

Another consideration is to remember to use the --hex-blob option in mysqldump. Otherwise, it could make things a little difficult to reload blob depending on certain sequences of characters.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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