Adding JPEG into MySQL table makes table nearly unmanageable

[Sparhawk]

I am working on creating a family photo album.
Due to certain requirements which I won't get into here, the picture files (typically JPEGs) need to be stored in the MySQL database instead of just as a file on the server. Picture files will range up to 400Kb each.

Using OPS' mysqladmin to edit the database fields, and even upload the file (as a LONGBLOB in Binary format) works great. But, once the file is uploaded, it takes a long time to bring up the page to edit the database fields.

The bigger the file is, the longer it then takes mysqladmin to let me into the page to edit the database fields. And then, when I submit the changes (I am not changing the picture file), it again takes a long time to submit.

Is mysqladmin actually loading the full picture into memory each time I want to edit the table?

[Neat Pete]

It depends how you are getting the data for display, presumably with the intention of updaying it.

If you are getting the data with something like SELECT * FROM Images_Table, or populating some control with the name of the table, then the answer is yes.

If you are just naming and displaying fields like DateTaken and PhotographerName and PictureCaption, then you are not loading the image into memory.

You also need to look at your WHERE clause - if it is returning more than one row, then you might be getting more than one picture at once. Certainly you might be getting too much data.

I read you are using mysqladmin - if you are editing the table then I suspect the answer is yes, but way round this is to build a query or a view and edit that.

Today I have been reading the PHP manual sections about handling JPG images. Lots of users have contributed programs to make thumbnails - the consensus is to make all pics into a thumbnail in advance, where possible, and only display thumbnails initially. It's a PHP manual, so there was nobody running with their pics inside a database. They all were using the concept of clicking on the thumbnail to see the larger pic. Gee, just like WikiPedia. Also they were all having problems with giant pics straight out of the camera, and being uploaded by users who have not a clue what's entailed. They all want to automate all the steps, so a medium size pic and a thumbnail are made automatically when a big pic (of any format or any size) is uploaded. My next work project exactly - in the worst instance the biggest client has just sent in 3000 pics on a DVD (about a third of them over 1mb, and there are 200 clients, each about 500 pics.

An alternative to putting your pics in a database might be to "hide" them in a directory that is not accessible from the net.

[spamjim]

You may want to try to set up your own local test server to experiment with how efficiently MySQL is supposed to handle binary data. Since April, when Powweb first announced its problems with MySQL, it seems that making any significant use of MySQL services here is not ideal.

[BerksWebGuy]

I always save the images elsewhere in the web directory, then keep the path or filename in mysql, and link to it that way.

[YvetteKuhns]

I always save the images elsewhere in the web directory, then keep the path or filename in mysql, and link to it that way.

That is how I do it, too.

[Neat Pete]

How did this one end up then?

[Sparhawk]

I ended up following BerksWebGuy's suggestion.
It works smoothly enough.

Also, I am storing just a 150px-wide image thumbnail in the MySQL database.

[Neat Pete]

Thanks.