I run a site which has a couple of million photos and gets over 1000 photos uploaded each day. Up to now, we haven't kept the original file that was uploaded to conserve on space. However, we are getting to a point that we are starting to see a need to have high-res original versions. I was wondering if its better to store these in the filesystem as an actual file or if its better to store them in a database (ie: mysql). The highres images would be rarely referenced but may be used when someone decides to download it or we decide to use it for rare processes like making a new set of thumbnails sizes/etc.
-
2Relevant: stackoverflow.com/questions/3748/… and dba.stackexchange.com/questions/736/…– wklCommented Mar 27, 2012 at 16:00
-
2As @birryree points out, this question is asked a lot. The answer is always generally the same; store large images in the file system. You can't run a WHERE clause on a picture. The only good reason to store pictures in a database is to provide rapid-fire access to them for displaying thumbnails and other smaller image artifacts. SQL Server does have a special FILESTREAM data type, but that's not really relevant to your question, since you use MySQL.– Robert HarveyCommented Mar 27, 2012 at 16:08
-
2Technically this is on topic, as it's a design question. However, as @birryree points out it has been asked on Stack Overflow and DBA many times before.– ChrisF ♦Commented Mar 27, 2012 at 16:10
-
Note that you need some encoding scheme to avoid having too many files in a single directory.– user1249Commented Apr 26, 2012 at 8:27
-
1Possible duplicate of Store images in the database, or in files with a database link?– Bart van Ingen SchenauCommented Oct 2, 2016 at 8:03
|
Show 1 more comment
2 Answers
Yep, as everyone has noted, store the large images in a filesystem (and possibly thumbnails as well). You can hash or UUID them or something to get a reasonable location/unique name, then you can store this (as well as the matching thumbnail or other resolutions if required) location in the database. Then the DB does the search/match/join work for you, and the filesystem does what it's good at: storing files.
-
1Btw, the "hash" can be broken down into subsets so that you can sort these files into structured directory trees to avoid having too many files in one place. You could break it down two or three characters at a time so there's never more than 100-1000 entries in any individual directory. Commented Apr 27, 2012 at 1:37
Without knowing it the question you are asking is very closely related to content management. Part of the domain of content management is managing attributes of that content - that is your problem here.
Store the images wherever you like that serves your purposes. What you need to do is develop a model for defining an authority for meta-data and variants of the images you produce.
Let's say I have product images. I have a data-development staff, a sales staff, a customer service staff, and an IT staff.
- My sales staff may want to add images of products that they take when they review the product and post it on forums.
- My data development staff needs to be able to find images from our suppliers, ftp, web and associate them with the products for use in our website.
- My customer service staff needs to be able to mark images as incorrect, and indicate when customers complain about images not being correct or representative of an item.
- My IT Staff needs to be able to know which image is the hi-res, which image applies to which product, and needs to be able to seamlessly create any resolution-version of the image at any point in time. As requirements for image sizes on our website change over time the process of generating new sizes should be as automated as possible.
The KEY to this problem is that there must be a database that contains information about all of the images in your organization and the standards you create to affect those images must be reflected in the database that describes all of your images.
When the time comes that you have too many images, (terabytes) and you want to archive images older than 6 years your database will let you know which images, and all variants of those images that need to be archived. You can update the database to indicate those images that are archived (and where) so the image can be restored again if it's truly necessarily.
