Ok, I have a question. I'm building a photo gallery for an aviation fansite, where people can upload photos they take. All the photo information (including the photo itself) is stored in a MySQL database. I did this for ease of use, and also to be able to limit who can view the photos, since no direct link to the photo exsists. However, what is the feasible limit of such a design? What if we get 500,000 photos in there? How slow will the database be? We're on a dedicated Core 2 Duo with 4GB of RAM, running CentOS 5.2 64bit, and two 750GB drives. Obviously the drives will need to be upgraded later. Is it better to use a database design where the database stores photo information, but the photos are physical files on the server? Still, the DB will be accessed just as much - but it won't be serving the complete photo every time. This is my first time undertaking such a huge project, so I want to make sure I get it as right as I can from the start. BTW, I'm making use of a lot of AJAX, too - so people can post comments on the photos, edit photo data, etc all on the same page. However, those are small database queries, so its no big deal. The majority of the db queries will be fetching the photo data. Maybe I should implement a sort of cache, so the DB won't have to work so hard? Any input would be greatly appreciated! Thanks!
Well the biggest problem I see here is dumping the DB, as it will get very huge the more photos you will need to store. Another thing are thumbnails, do you plan to use such? If so you will have to store photo + thumb, so calculated with low values of 150kb per foto (which is very small) and 50kb per thumbnail will mean about 20GB ot tablespace just because of storing fotos there. I don't want to know how much time it takes restoring the DB in case of a crash. I'd suggest storing the images + generated thumbs on a hdd. This will also give you the flexibility to e.g. move the images to a completely different server once as there you will need storage more than a fast cpu. Regarding the direct access, it's not a big deal preventing the webserver to have access to the imagefolder. Instead code a script serving the pictures. Fetching images is not a big deal for a DB as a DB is used to do complex queries. It's like using a sledge-hammer to crack a nut.
Well, I'm glad I decided to switch to file-based, then. I was worried about a few things, namely the backing up the database, and what would happen if the database crashed - how long would it take to restore it, etc. Thanks for the input!
