Hello Ilya, I think store files in DB has no complicated reasons, just for convenience. For example, I might make files automatically be backup-ed, and no budget for an independent backup solution. Thanks. Cui
2010/2/11 Ilya Kazakevich <kazakev...@devexperts.com>: > Hello John, > > There was a joke in russian PHP club: "why strore images in database? Are > you going to have a full text search on them?" > > IMHO: > Storing files in DB is probably bad idea. > Here are some advantages of storing files on filesystem: > 1) Theoretically it takes more time to load data from database then to load > it directly from filesystem if data is huge (we are not speaking about > databases, installed on raw disks) > 2) while storing files on fs, you can access them via web server and ftp and > network fs and what-ever-you-like. Almost all servers for your platform > supports filesystem, but not database. > Yes, its possible to create script to access data from db via web, but you > have to create it and support HTTP cache (modified-sience, e-tag and so on) > and you also need to store modification date in db (to make this cache work) > and so on. If you store files as files -- you have filesystem and servers to > access them. If you use db as filesystem -- you should create servers by > yourself. Looks like invention of wheel. > 3) Tables with BLOB are not so flexible, i.e. HEAP tables do not support > such fields. So, in some situation you'll have "filesort" that you cannt > fix. > 4) You can not easly do "SELECT *" from table with 10.000 rows if each row > contains field with 200Kb pdf file. > > And there is only one disadvantage: consistency. When deleting user -- all > her data could be deleted automatically using foreign keys, but you have to > do it in your app if files are stored externally. > > If somebody knows more reasons to store files in DB -- post it here, please. > It would be interesting. Thanks. > > Ilya. > > -----Original Message----- > From: John G. Heim [mailto:jh...@math.wisc.edu] > Sent: Wednesday, February 10, 2010 10:09 PM > To: mysql@lists.mysql.com > Subject: how things get messed up > > About 5 years ago, I was asked to write a php app for my department. The app > keeps track of graduate school applicants to my department at the > university. The main data elements are the scores each professor gives to > each applicant. There are only about 400 applicants each year so even with > all the personal data, scores, transcripts, etc for each student, it's not > much. for the first 2 years, it was under a meg of data. Well, then the > selection committee asked me to add something so that if a student e-mailed > the department a document, say a paper he'd written or a photo of himself, > or whatever, it could be tacked on to the info they saw about him while > grading the applicant. > > So I said, "Well, there is only going to be maybe 10 or 20 of those a year. > And even if all 400 applicants submit a PDF of a paper they'd written, it > would be only 400 docs. 4,000 after 10 years. Yeah, lets just create a > documents table in the database and store them in mysql." > > For the first 2 years, only 2 students sent in documents to attach to their > application. I figured I'd wasted my time. Then the next year, the graduate > school changed their web application form to allow students to upload > documents. "Fine," I said, "My worst case scenario has already come true. > But, well, this is why you plan for the worst case." > > Then they started taking letters of recommendation as PDF documents. In > fact, they started requiring PDF docs. Each student has 3 to 6 letters of > recommendation. All in all, I figure we're at about 100 times as many docs > in our database as I originally expected and about 10x my worst case > scenario. > > I should either be fired or shot. Maybe fired *then* shot. Actually, its > not as bad as all that. I can pretty easily write a perl script to export > the docs to files and access them via a network mounted filesystem. After > all, saving myself 5 hours of work 5 years ago is worth what? -- maybe > 10hours today? It is amazing how often quick & dirty turns out just being > dirty in the end. > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=kazakev...@devexperts.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=rancp...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
