What is the best way to store users images using PHP and MySQL?

Question

I was wondering what is the best way to store a users upload images like an avatar and so on using PHP and MySQL? Where should I begin? And is there a good article on this?

Answer 1

"Best" depends on what your goal is.

The two primary ways of storing user-uploaded images are either putting the binary content into the database as a BLOB, or storing the images to the drive somewhere and putting an entry into the database indicating which image belongs where.

Placing the images in the database has the advantage of not requiring any sort of filesystem permissions on the webserver, and removes any sort of syncing issues if you're serving up the site off of multiple webservers. However, over time it makes your database huge, and if you don't design your tables correctly, it can absolutely kill your performance and scalability.

Storing the images as file on the file system has the added advantage of making retrieval extremely quick and efficient, since webservers are very good at serving static files.

Edited to add

If you decide to store file content in the database, absolutely do not put it in a table that needs to be accessed quickly. If, for example, you have a "users" table that is searched on nearly every pageview, then that table is not the place to put your file contents. Instead, create a separate "images" or "files" table containing the file and related meta-information.

Putting a lot of bytes per row into a table makes that table very slow to work with. You don't want that kind of thing in tables that see heavy use.

Answer 2

Images should really be stored on the file system for a couple of reasons:

• Proxying and If-Modified-Since web requests: Apache can process If-Modified-Since HTTP headers for you and return a 304 response, and that's about the best performance you can get. Reverse Squid proxies and proxies posted at ISPs will attempt to take advantage of this.

• Virus scanning: if you allow any file uploads, jerks will try and upload scary stuff to see if they can bust your site. It's not unreasonable to want to run ClamAV or the like against your user uploads to see if there's trouble afoot. You wouldn't want to tie up your database if you wanted to scan the records for malware.

• Schema simplicity: If you allow file uploads, you'll also need to add meta data about the MIME-type, file size, height and width. If the file itself doesn't match the MIME-type in the table, then you need to code a select from the table and stream it into /usr/bin/file. It can be much simpler to shell_exec( "/usr/bin/file /path/to/mumble" ).

• Thumb-nailing: user image uploads are likely to need to be thumb-nailed, and this is often much easier done asynchronous to the actual web request. It's really not fun when some well meaning user attempts to upload a 150MB photoshop file given to them by their professional photographer buddy, and your apache instance goes OOM when attempting to load the ImageMagick library in the memory space of the web worker. This really doesn't scale for apache workers. Create a work queue/cron job outside of Apache to handle this work.

• Table corruption: Wow, you don't really want to cripple all user avatars if your MySQL index file gets borked and you need to do an offline table repair on that table.

• Backup and restore: You don't really want to lock a large table with mysqldump. Using rsync will save you a lot of time and give you much more flexibility. Tables are typically restored a whole table a time--tables are not typically backed up in smaller pieces.