Normal image storing or mySQL blob?

Question

Hi,

i'm programming a php/mysql web app which is kinda like a blogging platform where people can upload pictures and post them.

What is the best way to store these images , Storing them in a folder or in mySQL table through BLOB ?

i ask this because i want the most simple way which let me easily move to another host/server without spending days to download all posted pictures , and upload them to the new server.

Optional Question: Is a dedicated server enough for a started blogging platform?

Thanks

Answer 1

The best way is to store images in directory and store only the image name in database. In this way the database won't grow huge and will be more maintainable...

Answer 2

In my web-apps, i store binary files out of the data base. The implementation is more complex but the database is space save.

Answer 3

Out of the database. When it's out of the database your webserver can do its job and make clients cache the images. Doing so with a database-driven dynamic image is more complex. Additionally, you'll find you'll likely get much better performance by allowing the webserver to deal with it.

Answer 4

Save them as files:

• It's faster.

• You can handle files (copying to another server) without even accessing the database.

With a clever design of your application, you can even avoid any database access concerning these images. Cached template accesses the pictures or with clever filenaming.

Answer 5

If your providing hosting of static files, you may want to consider offsiting the files with a CDN such as Amazon S3. The implementation is more work, but you never have to worry about moving the files if you change web hosts (unless you change cdn provider). Plus file access will be localized to the client and won't even need to hit your web server for most requests, thus reducing bandwidth usage.

Answer 6

If it is images - the database. The performance can be as good as file system especially if you retrieve the file together with the file name and you preserve data integrity which is much more important than performance at least for business apps and regular websites (i.e. not youtube, gmail, microsoft.com, etc.)

here is more on the topic: http://sietch.net/ViewNewsItem.aspx?NewsItemID=124

One thing that you have to test and research yourself is how MySQL handles blobs. If it was SQL Server or Oracle I'm positive that they are doing pretty good job with blobs and especially the special filestream and bfile types. I don't have any experience with MySQL and don't really know if it can handle more than several megabytes of blobs.

Answer 7

Don't use the database - use the filesystem. I had this same problem, tried both approaches and in all honesty there was far too many issues with using the db as a binary data store.

What I do instead is have a folder that contains all uploaded files, with a reference database and object that relates to the uploaded files, but rather than just store them 'as is' I instead store then with the filename being the corresponding MD5 (or SHA-1) checksum of the aforementioned file, which quite nicely eliminates duplicate uploads and cuts down on overheads - require (); is more efficient than dumping the data from db.