I just started learning about MySql in order to start building
a website that stores a database of user information. I'm thinking
that I want my database to keep track of user accounts, user profiles,
and user information, etc.. etc...

I am planning to allow each user to upload as many photos as
they want into their personal account space, but my problem is
that I don't know WHERE to store an unknown number of images
in each user account. Can I store a collection of images in the
MySql database somewhere? How? Also, what are some other
ways to do this?

Thank you!

Robert Crandal wrote:

There are different ways you can do this, the more traditional way would be to
have a directory where you create one directory per user (say the user name)
and then store that users images there.

You could also store the images in a database table

CREATE TABLE userimages(
id int unsigned auto_increment primary key,
userid int unsigned not null,
name varchar(120) not null,
comment text,
image blob not null
);

The trickier part will be displaying the image, as you will need to have some
kind of script which fetches the image from the database and outputs the raw data.
I would recommend that the script fetches based on the id of the image, it may
include access checks too (if you want to allow the user to set different
access modes on images like "private", "public", "friend only").

If you are using a web hotel, then you may prefer to do the first option to
store directly to the disk, as you usually will have a lot less space to use
for your database than for your other files.

Keep in mind, don't do any "SELECT * FROM userimages" as you will fetch all
the image data too, if you want to create a list, then just use "SELECT id,
userid, name, comment FROM userimages", this will be lighter for your page,
specially if you have loads of images stored in the database.

--

//Aho

"J.O. Aho" <user (AT) example (DOT) net> wrote

In your opinion or best guess, how do you think Facebook does this?
Do you think Facebook creates a directory per user where each user
can upload his personal photos?

Robert Crandal wrote:
I would think the images are stored on disk and served by a static content web
service, where access is limited by configuration settings.

When I was working with an on-line web based game with 800k users, all images
were served by lighttpd while all dynamic content was served by apache. All
dynamic data was middle stored in memcached, so that pages would load far
faster as you didn't need to fetch the data from a database (and you can
settle with a cheaper database server solution).

Nowadays many of the big ones are using custom versions of apache, lighttpd
and mysql or even self developed applications to make the sites as fast as
possible.

Personally I prefer to have images on disk than in database, and I think there
will be a slight overhead with the database (I know Jerry will tell you
differently), but do your own tests and see what you think it faster and
easier to handle and use that.


--

//Aho

Robert Crandal wrote:

see BLOB data type, LOAD_FILE SQL statement, and memory limit for what
you have to tune.

As its a one to many relation, you need fields for ID, image, any
descriptive stuff, and a field to say who the image belongs to.

Robert Crandal wrote:

You can store binary data (like your images) as BLOBS in the database, but
quite frankly, I think this is a bad idea.

The way I would recommend is store the images on the filesystem, and store
links (either unix path names or the url that your webserver presents) in
the database.

Reasons:

If you server images out of the database, every time you display one, you
will have to have a web handler that gets the BLOB from the database, and
feeds it out via the web server. If you just have to provide a link, you can
have a simple web server setup that hands out the images direct from disk
which will be far simpler and more efficient.


--
Tim Watts

On 4/24/2011 2:47 AM, Robert Crandal wrote:
People have been using relational databases to store images for over 25
years that I'm aware of - we were doing it at IBM in the mid-80's.
They store images just fine, and the more images you have, the better
they do. Just use a BLOB datatype.

The only downside is you can't serve the image directly from the
database, but it just takes a short script do do so. Not a real big thing.

There are a lot of advantages to using a database. For instance, if you
just store the filename in the database, what happens when someone
deletes the image? Or what if the filename is deleted from the database
but the file still exists (orphaned file).

Also, file systems don't do well with thousands of users and hundreds of
thousands of images - they just weren't made to handle that many
individual files. RDBs such as MySQL, OTOH, easily handles millions of
users and hundreds of millions of images.

Remember- a file system is just a non-relational database. It's good
for some things but not everything.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle wrote:


That rather depends on the filesystem.

XFS will happily handle 13TB with over 10 million files - that is from
personal experience.

Obviously VFAT would be a bad idea, and putting *all* the files on one
directory *may* be a bad idea (depends on directory hashing support in the
FS) - but with a sensible layout (one dir per user, possibly with a further
sublayer of user's first letters a-z) it will be fine.

Couple of points IMO - and I cannot speak for MySQL in particular - but what
will it do for backups?

With a small database and a big filesystem of images, the DB dump will be
fine and the FS can be backed up with an rsync type methodology.

With a huge number of images in a DB, I suspect the possibilities of doing
incremental backups are vastly reduced and your DB dump file is ging to be
huge.

Just my opinion, based on Postgresql - but the principles mostly apply to
MySQL unless it has some particular features to help with the problems I
posed.

Cheers,

Tim
--
Tim Watts

On 4/24/2011 9:45 AM, Tim Watts wrote:
Such a layout is very hard to maintain and takes considerable care to
keep from getting screwed up. With a database, no special programming
is required, other than a small (< 10 line) script to serve the image
from the database.

Much better, because the files are backed up when the database is backed
up. Much less chance of referencing files from the database that don't
exist, or having orphaned files (files not referenced by the database).

Databases can be backed up quite easily. But rsync methodology does not
solve the problems noted above.

Not that bad, but if you really want, you could do incremental backups.

However, I always do full backups of both the file system and databases
- incremental backups are fast and take less space, but are very tedious
and error prone to restore because you have to go back to the last full
backup then restore from the incremental backups in order.
Additionally, incremental backups done with rsync usually end up
restoring files which had been deleted, wasting more space.

Storage is cheap.

Just my opinions based on 25 years of experience, starting with DB2 but
also including MySQL, Oracle and SQL Server.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 24-04-2011 14:42, Jerry Stuckle wrote:
People are useing filesystems to store images even longer than this 25
years.....

That has nothing to do with filesystems or databases, but more with
security,
What is someone does run a SQL script on your database like:
DELETE FROM table_with_images;


I dont think there is a relation between the images, ...?
so storing them in a database wont give any advantage.

--
Luuk