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]

Re: photos stored in database or as files

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.]

Re: photos stored in database or as files


U¿ytkownik "Toby A Inkster" <usenet200705@t obyinkster.co.u knapisa³ w
wiadomo¶ci news:llnnj4-h76.ln1@ophelia .g5n.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]

Re: photos stored in database or as files

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]

Re: photos stored in database or as files

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.
jstucklex@attgl obal.net
=============== ===

[K.]

Re: photos stored in database or as files


Uzytkownik "Jerry Stuckle" <jstucklex@attg lobal.netnapisa l w wiadomosci
news:Ys6dnQhGOe FxXvTbnZ2dnUVZ_ iydnZ2d@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]

Re: photos stored in database or as files

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]

Re: photos stored in database or as files

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.
jstucklex@attgl obal.net
=============== ===

[Toby A Inkster]

Re: photos stored in database or as files

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]

Re: photos stored in database or as files

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.
jstucklex@attgl obal.net
=============== ===

[Toby A Inkster]

Re: photos stored in database or as files

Jerry Stuckle wrote:

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

I'd imagine that a good number do, though only a few would use it for their
shared hosting servers. ReiserFS in particular is very popular for mail
queues (and news spools) as they tend to involve lots and lots of small
files.

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

URLs in demiblog

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