19

I'm coding an application that will be uploading and deleting many files, i usually just move the files to a folder in the server naming them with the row unique id. But as i understand MySQL also lets me store binary data (files) when would this be a better choice?.

Please use solid arguments, like When does using BLOB will mean performance improvement?.

P.S: I'm using MyISAM if that matters.

Thanks.

UPDATE:

Related questions:
- Storing Images in DB - Yea or Nay?
- To Do or Not to Do: Store Images in a Database (thanks to Sebastian)

UPDATE 2

Storing the files in the database is not a need i'm trying to know when is this a better idea than storing them in folders.

Community
  • 1
  • 1
amosrivera
  • 26,114
  • 9
  • 67
  • 76

5 Answers5

13

Read:

which concludes

If you on occasion need to retrieve an image and it has to be available on several different web servers. But I think that's pretty much it.

  • If it doesn't have to be available on several servers, it's always better to put them in the file system.
  • If it has to be available on several servers and there's actually some kind of load in the system, you'll need some kind of distributed storage.
Community
  • 1
  • 1
Sebastian Zaklada
  • 2,308
  • 1
  • 14
  • 25
  • 1
    Even in that case, I think memcached might be a better way to "distribute" the image file - you keep the image in one location/server, and store it in memcache upon a hit and use it across multiple web servers/requests. – Suman Apr 03 '12 at 22:18
  • @Suman memcache provides low latency which is good for fetching multiple small data objects but that advantage gets negligible the bigger the data when the roundtrip only makes a fraction of the transition time and operating systems are alreadey good at keeping files in RAM when they're keep being used. – Jimmy T. Feb 14 '21 at 19:54
9

If you are using MyISAM db engine then BLOB fields can be indexed so you can perform quick searches on your files using the database.

Also another advantage of storing files in BLOB fields is that they can be accessed more efficiently than files on the disk (there is no need for directory traversal, open, read, close).

If you are planning to store lots of files in MYSQL, it's usually a good practice to have the files stored in a separate table. This allows you to scan the meta info without stumbling over the blobs. Then, when you actually need to fetch a blob, the JOIN is adequately efficient.

Roman
  • 10,309
  • 17
  • 66
  • 101
  • So, which one is better in what situations? – amosrivera Mar 12 '11 at 22:26
  • You can say which method is better when you know your application needs but since you are using MyISAM tables then storing your files in the DB may give you more flexibility. – Roman Mar 12 '11 at 22:30
2

Well, it's a bit old, but this article makes a few decent arguments for BLOB storage: http://www.dreamwerx.net/site/article01.

While not a performance gain per se, having your images and whatnot in a DB as opposed to in a directory should also eliminate problems with hotlinking (assuming this is a web app that's publicly available).

Tieson T.
  • 20,774
  • 6
  • 77
  • 92
1

Memcache is not an alternative solution as you need to manage redundancy and TTL across distributed servers which make it harder to maintain.

The better solution in my opinion is to put public static data on CDN which is distributed by design and private static data on the DB for ease of distribution across multiple servers.

Each server can implement it's own Memcache upon each hit.

If you already stored data in the filesystem and you want to migrate it into database, the easiest way is to create a key,value table of the following:

KEY='/image/filename' (string of the filesystem location), value=BLOB (the actual file) and build a wrapper which will get this from the database with the help of rewrite rule and application handling. This way you can use full transparency with your existing code.

guykaplan
  • 145
  • 1
  • 3
0

Are you bound to using MySQL? If not, try an ODBMS or PostgreSQL to store files, or you could store just the paths for the files. See this for instance.

Community
  • 1
  • 1