Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have heard many times across the web that it is, for various reasons, a bad idea to store files directly in a database, the preferred method being to store filenames in the database, and files directly on the filesystem. Most explanations, however, seem to assume that the stored files (in my case 2MB longtext object containing XML) are important to retrieve along with the query. I've seen a few comments in passing, however, that suggest that by storing these files directly in your database you are bloating it and thereby "interfere with IO or query performance of the rest of your database."

Under what circumstances does this happen, and why?

It is my understanding that blob and longtext over a certain size were stored externally anyways. Does the database still attempt to load up the entire longtext of an entry when it's respective row is used in operations? My main concern here comes from the fact that most of the queries of the table containing these rows of longtext do not actually need the longtext xml for anything. Does simply having these cumulatively massive chunks of data sitting in the same table really affect the performance of other queries against that table?

share|improve this question
    
It turns out my problem was actually unrelated to the question I asked. For large tables, LIMIT OFFSET steps through the entries of the table sequentially, while "WHERE BETWEEN" performs the same operations in the expected time. –  Hawkwing Jul 23 '13 at 19:43
add comment

2 Answers

up vote 1 down vote accepted

InnoDB Architecture

InnoDB Architecture

This first thing that comes to mind is the InnoDB Buffer Pool. It holds three things

  • Data Pages from Accessed .ibd files
  • Index Pages from Accessed .ibd files
  • Insert Buffer for Changes to Secondary Indexes

Any query accessing infrequent data will basically remove the working dataset from the InnoDB Buffer Pool just to load the adhoc data. To get those data back, you have two options:

EPILOGUE

  • OPTION 1 is something you reluctantly live with until all subsequent queries bring back your most recent working dataset
  • OPTION 2 requires scripting adhoc reports to do the following
    • Backing up the InnoDB Buffer Pool
    • Run the adhoc queries on the infrequent data
    • Restoring the InnoDB Buffer Pool
  • OPTION 2 requires upgrading to MySQL 5.6
  • There are other ways MySQL and InnoDB are impacted. I discussed these in my recent post How to optimize MySQL for large BLOB updates
share|improve this answer
add comment

Discussion about the pros and cons, and about strategies, can be found at: http://programmers.stackexchange.com/questions/150669/is-it-a-bad-practice-to-store-large-files-10-mb-in-a-database

Having said that - 2MB isn't particularly large nowadays.

share|improve this answer
    
The problem wasn't that 2MB was large in its own right, but that there were hundreds of thousands of entries of this size. The application performed fine when it was still dealing with around 20k entries. –  Hawkwing Jul 22 '13 at 16:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.