photos stored in database or as files
K.
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_contents($_FILES['photo']['tmp_name']);
$data = mysql_real_escape_string($data);
// Preparing data to be used in MySQL query
mysql_query("INSERT 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
> 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" <usenet...@tobyinkster.co.uk> napisał w
wiadomości news:llnnj4-...@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
> 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.]
Jerry Stuckle
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.
jstu...@attglobal.net
==================
K.
Uzytkownik "Jerry Stuckle" <jstu...@attglobal.net> napisal w wiadomosci
news:Ys6dnQhGOeFxXvTb...@comcast.com...
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
> 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
> 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?
Toby A Inkster
> 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.]
Jerry Stuckle
> 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?
Toby A Inkster
> 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.]