http://qs321.pair.com?node_id=229248


in reply to Images into MySQL database

Keep in mind while its pretty cool to keep images and binary data in a database these solutions may be prone to scalability issues (at the very least, its inefficient).

To store binary data in a database you've got two hits:

  1. Reading the data from the database
  2. The database reading the data from the disk

The general solution employed is to have a pointer in the database pointing to your file in the filesystem.

Now what you have is a considerable reduction of data in the database which may easy be cached in memory, resulting in a performance increase.

Of course I'm generalising here, there are always exceptions.. :-)

Replies are listed 'Best First'.
Re: OT: Images into MySQL database
by Coruscate (Sexton) on Jan 23, 2003 at 09:02 UTC

    Keep in mind while its pretty cool to keep images and binary data in a database these solutions may be prone to scalability issues

    lol, this statement made me laugh quite a bit for one fact: does perlmonks not keep user images in the database? Each user image appears to have it's own node_id, so is this the case? Or does the node_id in this case just point to a file that the perlmonks code slurps in and outputs?

[reply]
Re: OT: Images into MySQL database
by CountZero (Bishop) on Jan 24, 2003 at 21:48 UTC

    From a general security point of view, I think it is inadvisable to have the database point to a file in the filesystem and then allowing the user access to your filesystem to retrieve the file. It could lead to all kinds of nasty problems.

    Also as database-engines are (should?) be optimised to retrieve data from their storage, I fail to see if it would be more efficient to first retrieve from the database the pointer to the file in your filesystem and then by a totally different process get the file itself.

    Or am I missing something obvious here?

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
      I think it is inadvisable to have the database point to a file in the filesystem

      I understand what you're saying here (someone can change a file to be something you dont want it to be) regardless of the optimisations in a modern RDBMS it is generally considered a more scalable solution to put the images on the disk and a pointer in the db.

      Of course if you've a low volume site that is lite on graphics, it may well be better to keep your graphics in a DB.

      Unfort, i dont have any metrics handy to backup my claims here..:-(

[reply]

        I agree with your point of view as long as we are strictly speaking within a webserver environment. Webservers are of course optimised to serve files (of whatever nature) and the security-issues can be well managed there.

        However, if you are just thinking of accessing the database directly (without going through a webserver first) I still think it is best to store the images in the database itself.

        Of course the reference to cgi.pm in the original question should have tipped me of that this was a web-server related matter.

        CountZero

        "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]

In Section Seekers of Perl Wisdom