This one time, at band camp, "Richard Lynch" <ceo@xxxxxxxxx> wrote: > On Tue, January 31, 2006 4:43 pm, tedd wrote: > >>Storing the image in the DB is probably not a Good Idea for a variety > >>of reasons. > > > > And they are? > > ... in the archives. > > http://marc.theaimsgroup.com/?l=php-general Rubbish. Storing images in the db is just as viable as within the fs. What is a file system if not a database? Most of the horror stories surrounding binary storage in BLOBs come from the use of database's that have no _true_ BLOB, like pgsql which uses a secondary database (r-tree) index portions of the image and then stitch them back together when SELECTed. Also many coders are at fault when doing things like SELECT * FROM image_table when all they need is the image name of image_id. My own benchmarks have shown MySQL to be on par with file system storage and the method of using the db to store the url proved to be the SLOWEST. Lets see... using this file <?php // just so we know it is broken error_reporting(E_ALL); $img = 'test.jpg'; $size = getimagesize ($img); $fp=fopen($img, "rb"); if ($size && $fp) { header("Content-type: {$size['mime']}"); fpassthru($fp); exit; } else { echo 'cannot display image '.$img; } ?> AB gives this result Time taken for tests: 44.576146 seconds Complete requests: 100000 Failed requests: 23 (Connect: 0, Length: 0, Exceptions: 23) Write errors: 0 Non-2xx responses: 100023 Total transferred: 49911477 bytes HTML transferred: 30406992 bytes Requests per second: 2243.35 [#/sec] (mean) Time per request: 0.446 [ms] (mean) Time per request: 0.446 [ms] (mean, across all concurrent requests) Transfer rate: 1093.43 [Kbytes/sec] received 44.5 seconds over 100,000 accesses when using the file system Now, using the db to store the url and the image in the file system... <?php // just so we know it is broken error_reporting(E_ALL); //connect to the db $link = mysql_connect("localhost", "username", "password") or die("Could not connect: " . mysql_error()); // select our database mysql_select_db("testblob") or die(mysql_error()); // get the url from the db $sql = "SELECT * FROM testlink"; // the result of the query $result = mysql_query("$sql") or die("Invalid query: " . mysql_error()); //grab the info from the db while($row=mysql_fetch_array($result)) { echo '<img src="'.$row['image_url'].'">'; } // close the db link mysql_close($link); ?> AB gives this result... Time taken for tests: 45.793727 seconds Complete requests: 100000 Failed requests: 1 (Connect: 0, Length: 0, Exceptions: 1) Write errors: 0 Non-2xx responses: 100001 Total transferred: 50300503 bytes HTML transferred: 30800308 bytes Requests per second: 2183.71 [#/sec] (mean) Time per request: 0.458 [ms] (mean) Time per request: 0.458 [ms] (mean, across all concurrent requests) Transfer rate: 1072.66 [Kbytes/sec] received 45.8 seconds over 100,000 accesses Now, using the database to store the image <?php // just so we know it is broken error_reporting(E_ALL); //connect to the db $link = mysql_connect("localhost", "username", "password") or die("Could not connect: " . mysql_error()); // select our database mysql_select_db("testblob") or die(mysql_error()); // get the url from the db $sql = "SELECT * FROM testblob"; // the result of the query $result = mysql_query("$sql") or die("Invalid query: " . mysql_error()); //grab the info from the db while($row=mysql_fetch_array($result)) { // set the header for the image header("Content-type: image/jpeg"); echo $row['image']; } // close the db link mysql_close($link); ?> AB reports the following.......... Time taken for tests: 45.206154 seconds Complete requests: 100000 Failed requests: 0 Write errors: 0 Non-2xx responses: 100000 Total transferred: 50200000 bytes HTML transferred: 30700000 bytes Requests per second: 2212.09 [#/sec] (mean) Time per request: 0.452 [ms] (mean) Time per request: 0.452 [ms] (mean, across all concurrent requests) Transfer rate: 1084.43 [Kbytes/sec] received 45.2 seconds. Faster than the image/url method and about 0.000015 seconds slower than the file system method. Of course, greater speeds can be gained should you store the database on a RAW partition and thus eliminate further file system overhead. Happy hunting. Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
