32 messages in com.mysql.lists.mysqlRe: BLOB's - General Guidance
FromSent OnAttachments
David Blomstrom19 May 2004 13:18 
Greg Willits19 May 2004 14:00 
David Blomstrom19 May 2004 14:06 
jabb...@abbotts.org19 May 2004 14:19 
Michael Kruckenberg19 May 2004 14:30 
David Blomstrom19 May 2004 15:11 
Greg Willits19 May 2004 15:18 
Greg Willits19 May 2004 15:29 
Hassan Schroeder19 May 2004 15:49 
David Blomstrom19 May 2004 16:21 
artem19 May 2004 16:34 
Hassan Schroeder19 May 2004 16:37 
Udik...@aol.com20 May 2004 14:45 
Greg Willits20 May 2004 14:57 
Luis R. Rodriguez20 May 2004 17:47 
David Blomstrom20 May 2004 18:55 
jabb...@abbotts.org20 May 2004 19:24 
David Blomstrom20 May 2004 20:17 
SGr...@unimin.com21 May 2004 08:31 
Udik...@aol.com21 May 2004 11:25 
David Blomstrom21 May 2004 15:32 
Udik...@aol.com21 May 2004 17:01 
Erich Beyrent21 May 2004 18:52 
David Blomstrom21 May 2004 19:16 
Hassan Schroeder21 May 2004 20:01 
David Blomstrom21 May 2004 21:14 
David Blomstrom21 May 2004 21:47 
Hassan Schroeder21 May 2004 21:47 
Greg Willits21 May 2004 21:55 
Luis R. Rodriguez22 May 2004 23:19 
Joshua J. Kugler27 May 2004 01:25 
Robert J Taylor27 May 2004 02:02 
Subject:Re: BLOB's - General Guidance
From:Robert J Taylor (rob@rjamestaylor.com)
Date:05/27/2004 02:02:03 AM
List:com.mysql.lists.mysql

Joshua J. Kugler wrote:

There is one instance in which it is *not* convenient to store in seperate files: when you are exporting to another machine (maybe a sub set of data from an internal server to an external web server) or doing replication.

Very true. We use MySQL to store our blobs for a JBoss application for this reason (among others; the main one being the system architect likes it that way -- it's good to be king!).

j----- k-----

On Wednesday 19 May 2004 01:01 pm, Greg Willits said something like:

On May 19, 2004, at 1:19 PM, David Blomstrom wrote:

I'd like to get some feedback on storing images in MySQL databases. The stuff I've read so far suggests that it's fairly difficult to work with images in MySQL, and they also slow down databases.

One thing to remember when you have a blob (or text, iirc) column in a tuple (row, sorry; just Codding around and Dating myself) is that when MySQL examines the row the entire blob is loaded into memory even if that particular column isn't referenced in the query. So, use a split table for blobs -- a main table with the blob attributes for searching and a dependent table with its primary key set as a foreign key to the main table holding the blob for direct access to the blob as needed. This helped us emmensely.

I've also read that there isn't much you can do with BLOB's that you can't do with PHP manipulating images stored in an ordinary folder.

So I just wondered if BLOB's are worth my time. For example, I'm working on a database with information about the 50 states. If I have maps of each state, pictures of each state's capital, etc., is there some BLOB feature that I would find really useful?

In your case, a field holding the data particulars with a middleware parsible URN (a local filesystem path or remote URL, et al) to the blob or text body should suffice.

All "conventional wisdom" I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename &/or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues.

-- greg willits

Luckily, MySQL handles the bloat quite well in our experience, with the caveat that we don't include the blob in the search details table....

HTH,

Robert J Taylor rob@rjamestaylor.com