The most efficient way to store photo reference in a database

Question

I'm currently looking to store approximately 3.5 million photo's from approximately 100/200k users. I'm only using a mysql database on aws. My question is in regards to the most efficient way to store the photo reference. I'm only aware of two ways and I'm looking for an expert opinion.

Choice A

A user table with a photo_url column, in that column I would build a comma separated list of photo's that both maintain the name and sort order. The business logic would handle extracting the path from the photo name and append photo size. The downside is the processing expense.

Database example

"0ea102, e435b9, etc"

Business logic would build the following urls from photo name

/0e/a1/02.jpg
/0e/a1/02_thumb.jpg 
/e4/35/b9.jpg
/e4/35/b9_thumb.jpg

Choice B - Relational Table joined on user table with the following fields. I'm just concerned I may have potential database performance issues.

pk
user_id
photo_url_800
photo_url_150
photo_url_45
order

Does anybody have any suggestions on the better solution?

possible duplicate of [How to upload image PHP and insert path in MySQL?](http://stackoverflow.com/questions/18737462/how-to-upload-image-php-and-insert-path-in-mysql) — Mark, Oct 04 '13 at 02:38
@Christian Mark I'm not asking how to build a table, I'm looking for bench mark comparisons based on my above two options. I'm looking for the best option. — Code Junkie, Oct 04 '13 at 02:42
ok. given. but optimizing your table can optimize your queries. — Mark, Oct 04 '13 at 02:52
@Christian Mark of the two designs, which one do you think will have the best performance? — Code Junkie, Oct 04 '13 at 02:53

score 2 · Accepted Answer · answered Oct 04 '13 at 03:04

2

The best and most common answer would be: choice B - Relational Table joined on user table with the following fields.

id
order
user_id
desc
photo_url_800
photo_url_150
photo_url_45
date_uploaded

Or a hybrid, wherein, you store the file names individually and add the photo directory with your business logic layer.

My analysis, your first option is a bad practice. Comma separated fields are not advisable for database. It would be difficult for you to update these fields and add description on it.

Regarding the table optimization, you might want to see these articles:

answered Oct 04 '13 at 03:04

Mark

8,046
15
48
78

Thanks Christian, I use the Hibernate ORM, so with that said I think I'd just use the hybrid model. I would probably just store the photo name like I have in Choice A "0ea102" into the relational design, but in the JPA entity add a few method calls where I build out the URL's from the photo name "/0e/a1/02" append size "800" and extension giving me "/0e/a1/02_800.jpg" when I call the method getPhotoSize800(). I could just store all the sizes in directory a1. I like the idea of having them in the relational table do to the fact it makes it much easier for the photo cleanup job to run. Thoughts? – Code Junkie Oct 04 '13 at 03:20
1

Yes, that is correct. In that way you will also solve some migration issues. I've experience it once and it works effectively. – Mark Oct 04 '13 at 03:34
1

Thanks Christian, see my post below and you'll see my hybrid model. – Code Junkie Oct 04 '13 at 03:47

Code Junkie · Answer 2 · 2013-10-04T03:54:26.813

Here is an example of my final solution using the hibernate ORM, Christian Mark, and my hybrid solution.

@Entity
public class Photo extends StatefulEntity {

    private static final String FILE_EXTENSION_JPEG = ".jpg";
    private static final String ROOT_PHOTO_URL = "/photo/";
    private static final String PHOTO_SIZE_800 = "_800";
    private static final String PHOTO_SIZE_150 = "_150";
    private static final String PHOTO_SIZE_100 = "_100";
    private static final String PHOTO_SIZE_50 = "_50";   

    @ManyToOne
    @JoinColumn(name = "profile_id", nullable = false)
    private Profile profile;

    //Example "a1d2b0" which will later get parsed into "/photo/a1/d2/b0_size.jpg"
    //using the generatePhotoUrl business logic below. 
    @Column(nullable = false, length = 6)
    private String fileName;

    private boolean temp;

    @Column(nullable = false)
    private int orderBy;

    @Temporal(TemporalType.TIMESTAMP)
    private Date dateUploaded;

    public Profile getProfile() {
        return profile;
    }

    public void setProfile(Profile profile) {
        this.profile = profile;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public Date getDateUploaded() {
        return dateUploaded;
    }

    public void setDateUploaded(Date dateUploaded) {
        this.dateUploaded = dateUploaded;
    }

    public boolean isTemp() {
        return temp;
    }

    public void setTemp(boolean temp) {
        this.temp = temp;
    }

    public int getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(int orderBy) {
        this.orderBy = orderBy;
    }

    public String getPhotoSize800() {
        return generatePhotoURL(PHOTO_SIZE_800);
    }

    public String getPhotoSize150() {
        return generatePhotoURL(PHOTO_SIZE_150);
    }

    public String getPhotoSize100() {
        return generatePhotoURL(PHOTO_SIZE_100);
    }

    public String getPhotoSize50() {
        return generatePhotoURL(PHOTO_SIZE_50);
    }

    private String generatePhotoURL(String photoSize) {
        String firstDir = getFileName().substring(0, 2);
        String secondDir = getFileName().substring(2, 4);
        String photoName = getFileName().substring(4, 6);

        StringBuilder sb = new StringBuilder();
        sb.append(ROOT_PHOTO_URL);
        sb.append("/");
        sb.append(firstDir);
        sb.append("/");
        sb.append(secondDir);
        sb.append("/");
        sb.append(photoName);
        sb.append(photoSize);
        sb.append(FILE_EXTENSION_JPEG);
        return sb.toString();
    }

}

The most efficient way to store photo reference in a database

2 Answers2

Linked