Databases
StackOverflow2013
Postsdbo
POHow to store images in your filesystem
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to store images in your filesystem
    primarykey
    data
    text
    <p>Currently, I've got images (max. 6MB) stored as BLOB in a InnoDB table. As the size of the data is growing, the nightly backup is growing slower and slower hindering normal performance.</p> <p>So, the binary data needs to go to the file system. (pointers to the files will be kept in the DB.)</p> <p>The data has a tree like relation:</p> <pre><code>- main site - user_0 - album_0 - album_1 - album_n - user_1 - user_n etc... </code></pre> <p>Now I want the data to be distributed evenly trough the directory structure. How should I accomplish this?</p> <p>I guess I could try <code>MD5('userId, albumId, imageId');</code> and slice up the resulting string to get my directory path:</p> <pre> /var/imageStorage/f/347e/013b/c042/51cf/985f7ad0daa987d.jpeg </pre> <p>This would allow me to map the first character to a server and evenly distribute the directory structure over multiple servers.</p> <p>This would however <em>not</em> keep images organised per user, likely spreading the images for 1 album over multiple servers.</p> <p>My question is:<br> What is the best way to store the image data in the file system in a balanced way, while keeping user/album data together ?</p> <p>Am I thinking in the right direction? or is this the wrong way of doing things altogether?</p> <p><strong>Update:</strong><br> I will go for the <code>md5(user_id)</code> string slicing for the split up on highest level. And then put all user data in that same bucket. This will ensure an even distribution of data while keeping user data stored close together.</p> <pre> /var - imageStorage - f/347e/013b - f347e013bc04251cf985f7ad0daa987d - 0 - album1_10 - picture_1.jpeg - 1 - album1_1 - picture_2.jpeg - picture_3.jpeg - album1_11 - picture_n.jpeg - n - album1_n </pre> <p>I think I will use albumId splitted up from behind (I like that idea!) as to keep the number of albums per directory smaller (although it won't be necessary for most users). </p> <p>Thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCan Vural
    1. USJacco
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to store images in your filesystem
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to store images in your filesystem
      1. USGulzar Nazim
      1. VTFavorite
    3. VO
      singulars
      1. POHow to store images in your filesystem
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAh - I'd suggest editing "nicely distribute" to "evenly distribute". I now realize that your goal is to try and average out the number of pictures per file system folder.
      singulars
      1. POHow to store images in your filesystem
      1. USJ c
    2. COthanks, I'm not native English
      singulars
      1. POHow to store images in your filesystem
      1. USJacco
    3. COHave you considered doing incremental backups of the DB?
      singulars
      1. POHow to store images in your filesystem
      1. USTahir Akhtar
 

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