The discussion came up yesterday as to whether images should be stored in plain files or in a database, with one of my delegates saying that he had been strongly advised against a database.
That advise is typically going to be correct for a handful of images ... but when you get on to thousands in a single 'directory', the directory structure of operating systems is not best set up to serve all the individual files and a database solution is the best. The database solution also allows you to hold descriptions and other information easily with the images.
"So how big is your image database?" Well - there's just over 4000 images in it, and I guessed that the disc space occupied was about a quarter of a gigabyte on the server. As my data is stored using MyISAM tables, I was able to take a look in the evening and see that my estimate was remarkable close:
-rw-rw---- 1 mysql mysql 8674 2009-03-09 17:24 im_library.frm
-rw-rw---- 1 mysql mysql 243813840 2009-05-27 21:23 im_library.MYD
-rw-rw---- 1 mysql mysql 158720 2009-05-27 21:23 im_library.MYI
All the images from the database are 'fed' via a PHP script which initially checks for a 'real' file and if it finds it, saves it into the database keyed by the file name, and deletes the real file - so it's very easy to add images. My regular database backup backs up all my images. And mod_rewrite is used to send all requests ending in .jpg to the script.
(written 2009-05-28)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S161 - Data Access and Security in MySQL [192] Current MySQL and PHP paths and upgrades - (2005-01-28)
[193] The wrong MySQL - (2005-01-29)
[401] What is an SQL injection attack? - (2005-08-02)
[535] MySQL permissions and privileges - (2005-12-20)
[647] Checking for MySQL errors - (2006-03-15)
[947] What is an SQL injection attack? - (2006-11-27)
[1131] MySQL - Password security (authentication protocol) - (2007-04-02)
[2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
[2647] Removing duplicates from a MySQL table - (2010-02-22)
[3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
[4390] Checking MySQL database backups have worked (not failed) - (2015-01-10)
[4406] Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25)
[4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
[4491] Web Server Admin - some of those things that happen, and solutions - (2015-05-10)
[4493] Forgotten / lost MySQL root password - (2015-05-16)
S154 - MySQL - Designing an SQL Database System [59] MySQL - Pivot tables - (2004-09-22)
[361] Binary Large Objects or bars - (2005-06-27)
[375] Oops - I got my initial database design wrong - (2005-07-12)
[494] MySQL - a score of things to remember - (2005-11-12)
[515] MySQL - an FAQ - (2005-12-03)
[666] Database design - get it right from first principles - (2006-04-02)
[918] Databases needn't be frightening, hard or expensive - (2006-11-08)
[937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
[945] Code quality counts - (2006-11-26)
[1423] MySQL - table design and initial testing example - (2007-11-06)
[1575] Database design for a shopping application (MySQL) - (2008-03-15)
[1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
[2053] What a difference a MySQL Index made - (2009-02-25)
[2085] MySQL - licensing issues, even with using the name - (2009-03-16)
[2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
[3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
[3494] Databases - when to treat the rules as guidelines - (2011-10-23)
[4426] FileMaker Day to Unix Time conversion - (2015-02-15)
Some other Articles
A beautiful coastlineNorth Antrim Coast - a jewel thats well worth a visitSpot the odd one outAn evening walk in CarrickfergusImages in a database? How big is a database? (MySQL)Always use su with minus. And where do programs come from?Bright ColoursRunning straight from the jar, but not from a tarEngland, Scotland, Northern IrelandImproving the structure of your early PHP programs