MySQL driven photo gallery
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!