0

Is it faster to stream a picture/image from a file system or to stream it from a BLOB, from a MySQL database?

What I am acctually asking:

Will it be faster for PHP to read a picture from a table and output it to a browser, or to stream it directly from a file system?

  • 4
    Benchmark it? My bet would be on directly from filesystem (after all, MySQL is a layer of indirection above that). – eggyal Oct 22 '12 at 22:50
  • 2
    See [storing-images-in-db-yea-or-nay](http://stackoverflow.com/questions/3748/) – juergen d Oct 22 '12 at 22:51
  • 1
    In some ways those lines can start to blur. Take for example SQL Server 2008's file stream data type. http://msdn.microsoft.com/en-us/library/gg471497.aspx – ficuscr Oct 22 '12 at 23:03

2 Answers2

3

First of all: It will in most cases be faster to read it from the file system.

Everything starting with "First of all" needs either a "but" or an "additionally" - I'll try to provide both:

  • Additionally you will make your other queries slower, by using buffer pages for BLOB data, that could otherwise be used for data or indices.

  • But if you scale up to a more-than-one-server architecture, you will need a shared file system, which is a minefield in its own right. This sometimes (in special use cases, not routinely), is the argument that swings the jury towards storing BLOBS

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
0

Without any research it's IMO impossible to make a valid statement. This paper e.g. comes to the conclusion:

objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors.

And this applies only to their environment (SQL Server 2005). Anyways, I'd say the advantages of having resources in the DBS (e.g. ACID) would predominate if it wasn't for PHP which simply doesn't support streaming BLOBs since almost a decade (contrary to what the manual says).

Markus Malkusch
  • 7,738
  • 2
  • 38
  • 67