> > There are several reasons why you should consider not storing > binary data in > your database: > > [snip] > > You probably have several reasons why you would want to store > your images in > your database, despite all the statements above. Others have, > before you. And > they have all returned to the same solution: Store a link > (e.g. a file path) > to the image file in the database. Whenever you need the > image, use the link > in whatever program you use to retrieve the file containing the image.
Yes, but, this advice does *not* go on to describe how you cope with the deletion problem. If you store data in records in the database a DELETE will delete *all* the data for the set of rows. If some of the data is lying around in disk files these obviously don't get deleted by DELETE. So it seems to me that you need an image file garbage collection process, which every now and then scans the disk, scans the database and deletes disk files which no longer have a pointer to them from any reference field in any row in any table in the database. And of course as the file store and the database could both be changing under your feet whilst you're doing this it seems to be a good idea to shut down, or lock, the database server and the processes you've got adding disk files whilst you're doing the garbage collection. Doesn't sound to clever to me. Yes of course you could make your business logic layer clever enough to have a go at deleting the files when it deletes the database rows. But as you don't have transactions covering database operations *and* file system operations things will get out of step sooner or later so you'll still need the garbage collector. And some hints as to the viability of, say, hundreds of thousands of operating system files on the various platforms supported by MySQL would be helpful. For example many filing systems will lose *lots* of space through fragmentation like this, which one might hope would not happen with blobs. And what about the performance implications of lots of files in the same directory? - NT4 for example *will* handle half a million files in the same directory, 'cos I've tried it, but it's not terribly practical as some operations start taking tens of minutes instead of tens of milliseconds. Really one of the reasons for using a DBMS is to handle all this storage management crap for you, no? Tim Ward Brett Ward Limited - www.brettward.co.uk ------------------------------------------------------- On August 5th ip.access will be moving to: CPC1 Telephone: +44 (0) 1223 219000 Capital Park Fax: +44 (0) 1223 219099 Fulbourn Cambridge CB1 5XE United Kingdom ------------------------------------------------------- This e-mail and the information it contains are confidential and may be privileged. If you have received this e-mail in error please notify us immediately. You should not copy it for any purpose, or disclose its contents to any other person. All emails to and from ip.access may be monitored and stored for audit and other legal reasons. ------------------------------------------------------- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
