photos stored in database or as files

[K.]

Hello all!

I have a question to you.

I would like to create photo gallery.

I wonder if I should store photos (uploaded by users) in database

$data = file_get_conten ts($_FILES['photo']['tmp_name']);
$data = mysql_real_esca pe_string($data );
// Preparing data to be used in MySQL query

mysql_query("IN SERT INTO {$table}
SET ext='$ext', title='$title',
data='$data'");

$msg = 'Success: image uploaded';

or I should store uploaded photo files as files in the server folder?

Few years ago in my previous work I worked in MS Access application, and
in my opinion storing photos in this database are not so good, becuase
uploaded
photo files stored in MS Access application, enlarged the database a few
times.
That`s why storing such files in database is not so relevant and the better
way
was to saved these files in the specified folder.
What about Mysql?

Could you give me your opinions?
M.

[Toby A Inkster]

K. wrote:

I wonder if I should store photos (uploaded by users) in database

I would recommend not doing so. Storing them in files and then referencing
them by filename in the database is probably better. It will make for much
easier incremental backups.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 104 days, 18:13.]

URLs in demiblog
http://tobyinkster.co.uk/blog/2007/05/31/demiblog-urls/

[K.]

U¿ytkownik "Toby A Inkster" <us**********@t obyinkster.co.u knapisa³ w
wiadomo¶ci news:ll******** ****@ophelia.g5 n.co.uk...

K. wrote:

>I wonder if I should store photos (uploaded by users) in database

I would recommend not doing so. Storing them in files and then referencing
them by filename in the database is probably better. It will make for much
easier incremental backups.

Yes maybe it is better for backups, but what about the size of the database
after uploading many photos?

Thank you for post
Marcin

[Toby A Inkster]

K. wrote:

Yes maybe it is better for backups, but what about the size of the database
after uploading many photos?

The database will remain small. Read my original post again carefully.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 104 days, 21:09.]

URLs in demiblog
http://tobyinkster.co.uk/blog/2007/05/31/demiblog-urls/

[Jerry Stuckle]

K. wrote:

Hello all!

I have a question to you.

I would like to create photo gallery.

I wonder if I should store photos (uploaded by users) in database

$data = file_get_conten ts($_FILES['photo']['tmp_name']);
$data = mysql_real_esca pe_string($data );
// Preparing data to be used in MySQL query

mysql_query("IN SERT INTO {$table}
SET ext='$ext', title='$title',
data='$data'");

$msg = 'Success: image uploaded';

or I should store uploaded photo files as files in the server folder?

Few years ago in my previous work I worked in MS Access application, and
in my opinion storing photos in this database are not so good, becuase
uploaded
photo files stored in MS Access application, enlarged the database a few
times.
That`s why storing such files in database is not so relevant and the better
way
was to saved these files in the specified folder.
What about Mysql?

Could you give me your opinions?
M.

I store images in databases all the time. It works well. Sure, the
files can get large - but no larger than the individual files put
together are. In fact, the database is generally smaller than the sum
of the files because the db can manage disk space more effectively.

If you do a lot of adding and deleting in the database your files may
become larger due to empty space, but databases are generally pretty
good at reusing that space where possible. You just may need to
compress the database occasionally if this occurs.

Databases work well with huge numbers of images - they're made to handle
large numbers of rows. File systems aren't. 100K rows to a database is
nothing. But try to put that many files in one directory.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[K.]

Uzytkownik "Jerry Stuckle" <js*******@attg lobal.netnapisa l w wiadomosci
news:Ys******** *************** *******@comcast .com...

K. wrote:

>Hello all!

I have a question to you.

I would like to create photo gallery.

I wonder if I should store photos (uploaded by users) in database

$data = file_get_conten ts($_FILES['photo']['tmp_name']);
$data = mysql_real_esca pe_string($data );
// Preparing data to be used in MySQL query

mysql_query("IN SERT INTO {$table}
SET ext='$ext', title='$title',
data='$data'");

$msg = 'Success: image uploaded';

or I should store uploaded photo files as files in the server folder?

Few years ago in my previous work I worked in MS Access application, and
in my opinion storing photos in this database are not so good, becuase
uploaded
photo files stored in MS Access application, enlarged the database a few
times.
That`s why storing such files in database is not so relevant and the
better way
was to saved these files in the specified folder.
What about Mysql?

Could you give me your opinions?
M.

I store images in databases all the time. It works well. Sure, the files
can get large - but no larger than the individual files put together are.
In fact, the database is generally smaller than the sum of the files
because the db can manage disk space more effectively.

If you do a lot of adding and deleting in the database your files may
become larger due to empty space, but databases are generally pretty good
at reusing that space where possible. You just may need to compress the
database occasionally if this occurs.

Databases work well with huge numbers of images - they're made to handle
large numbers of rows. File systems aren't. 100K rows to a database is
nothing. But try to put that many files in one directory.

OK. Thank you for the efficient explanaition. I understood everything.
It is logic and now I will know what I should do during builidng the
database.

[Christoph Burschka]

Jerry Stuckle schrieb:

Databases work well with huge numbers of images - they're made to handle
large numbers of rows. File systems aren't. 100K rows to a database is
nothing. But try to put that many files in one directory.

Databases are meant to store huge *numbers* of records that can be
quickly indexed and searched. They're not meant to store big blobs of
binary data.

So use the appropriate tool for *each* part of the job.

Nearly every good CMS that handles images will use a folder in the file
system combined with a MySQL table of file names. There is no practical
limit to the number of files in a file system; the only performance
issue you get is when you try to list all of them at once or search for
one.

But give it a specific path (which MySQL found in its table in an
instant) and the file system will find your file immediately, regardless
of how many other files there are in the directory.

--
cb

[Jerry Stuckle]

Christoph Burschka wrote:

Jerry Stuckle schrieb:

>Databases work well with huge numbers of images - they're made to
handle large numbers of rows. File systems aren't. 100K rows to a
database is nothing. But try to put that many files in one directory.

Databases are meant to store huge *numbers* of records that can be
quickly indexed and searched. They're not meant to store big blobs of
binary data.

And where did you get that from? They work just great for images.

So use the appropriate tool for *each* part of the job.

I do.

Nearly every good CMS that handles images will use a folder in the file
system combined with a MySQL table of file names. There is no practical
limit to the number of files in a file system; the only performance
issue you get is when you try to list all of them at once or search for
one.

So "nearly every good CMS..."? What does that prove? Absolutely
nothing, maybe? And when was the last time you tried to handle 100K
pictures in a single directory? Databases handle this quite easily.

But give it a specific path (which MySQL found in its table in an
instant) and the file system will find your file immediately, regardless
of how many other files there are in the directory.

No, it won't. Try putting 100K files in a directory and see how long it
takes to find one. Also, see how much space they take. Unless they're
each a multiple of 512 bytes, you'll get a lot of wasted space.

Now repeat with MySQL. Not only will retrieval be faster, the files
will take less space.

--
cb

You've obviously never tried it, have you?

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[Toby A Inkster]

Jerry Stuckle wrote:

Also, see how much space they take. Unless they're
each a multiple of 512 bytes, you'll get a lot of wasted space.

Certain filesystems (e.g. ReiserFS and JFS) use block suballocation
allowing multiple files or part-files to share the same block, which
makes the storage of many small files very efficient.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 109 days, 21:40.]

URLs in demiblog
http://tobyinkster.co.uk/blog/2007/05/31/demiblog-urls/

[Jerry Stuckle]

Toby A Inkster wrote:

Jerry Stuckle wrote:

>Also, see how much space they take. Unless they're
each a multiple of 512 bytes, you'll get a lot of wasted space.

Certain filesystems (e.g. ReiserFS and JFS) use block suballocation
allowing multiple files or part-files to share the same block, which
makes the storage of many small files very efficient.

Toby,

Yes, I understand. But there's still a larger amount of overhead in
those filesystems than there is with a database - even with an index.

Additionally, how many hosting companies are using ReiserFS and/or JFS?

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===