Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It largely depends on how many points of failure you want and the speed of the response time.</p> <p>If you store it on the filesystem:</p> <ol> <li>Incoming HTTP request</li> <li>PHP queries the database</li> <li>The database finds the row</li> <li>PHP deciphers the query response</li> <li>PHP reads the file</li> <li>PHP sends the file's binary contents as a response to the HTTP request</li> </ol> <p>However, if you store it as a binary blob in a database:</p> <ol> <li>Incoming HTTP request</li> <li>PHP queries the database</li> <li>The database finds the row</li> <li>PHP deciphers the query response</li> <li>PHP sends the binary contents as a response to the HTTP request</li> </ol> <p>In both cases step 3 (the database finds the row) can be just as fast with our WITHOUT the blob column as long as proper indices/keys are set up. MySQL will move the pointer to the exact indexed position internally - it won't actually go through every byte until it finds the right one (that's the whole point of an index). This is just as time consuming as PHP reading the file manually. However, I currently <em>do not</em> have supporting performance data to support this.</p> <p>Now let me talk about points of failiure:</p> <ul> <li>Let's say you migrate your data. If your binary image data is stored in the database, you just move the database. However, if your binary image data is stored on the file system, you have to remember to move both. Also note that the SIZE of the migration would be identical (or at least minimally different).</li> <li>Consider renaming an asset - you'll have to rename it not only in the database, but also on the file system. That's 2 total steps as opposed to renaming it in the DB which is only 1.</li> <li>Consider deleting the asset - you'll have to delete it in both locations.</li> </ul> <p>I have always stored binary data as a blob in the database for the purpose of portability, flexibility, and minimizing failure or corruption.</p> <p>If you choose to store the file in the database as a blob, consider the different blob storage requirements: <a href="http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html#id656744" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html#id656744</a></p> <ul> <li>TINYBLOB - 256 bytes</li> <li>MEDIUMBLOB - 64 kilobytes</li> <li>LONGBLOB - 4 gigabytes</li> </ul> <p>Lastly, just food for thought: I have experience working with Adobe's Day CQ which is an up-and-coming enterprise-level content management system. It is primarily written in Java, but what is important to note is the data architecture. It uses a JCR (Java Content Repository) which more or less acts like a multidimensional MySQL database (kinda cool?). All image data in its DAM (digital asset manager) is stored as a node within the JCR.</p>
    singulars
    1. This table or related slice is empty.
    1. POBest practise for handling images in a CMS
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USzamnuts
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POBest practise for handling images in a CMS
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks for the detailed response, to be honest I had decided not to store the actual binary data in the database. Although you do present some compelling arguments which I need to consider. What I was thinking was either just storing the filenames in a database table as opposed to using the id of the item alone to find the folder and simply include all images in that folder. Forgive my confusion in wording.
      singulars
      1. PO
      1. USadamK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload