I'm focusing on a content management application where the data being saved around the database is very generic. During this instance a container has numerous assets and individuals assets map with a type of digital resource, whether that be considered a picture, a film, an submitted file as well as plain text.
I've been quarrelling having a friend for any week now because additionally to storing the images, etc - they wish to keep text assets around the file system and also have the application lookup the file location(in the database) and browse within the text file(in the file system) before serving towards the client application.
Good sense appeared to scream at me this was absurd and when we're disturbing to find information about something in the database, we may as well keep text inside a database column and also have it offered along track of the row research. Database research + File IO appeared sounds uncontrollably reduced then just Database Research. After returning and forth for a while, I made the decision to operate some benchmarks and located the outcomes just a little surprising. There appears to be really little consistency if this involves benchmark occasions. The only real obvious champion within the benchmarks was tugging a sizable dataset in the database and iterating within the leads to display the written text resource, however tugging objects individually in the database and exhibiting their text content appears to become neck and neck.
Now we all know the restrictions of running benchmarks, and I don't know I'm even running the right concept of "tests" (for instance, File system creates are absurdly faster then database creates, did not realize that!). I suppose my real question is for confirmation. Is File I/O similar to database text storage/research? Shall We Be Held missing an element of the argument here? Thanks in advance for the opinions/advice!
A fast work by what I'm using: This can be a Ruby on Rails application, using Ruby 1.8.6 and Sqlite3. I plan on moving exactly the same codebase to MySQL tomorrow if the benchmarks are exactly the same.
I believe your benchmark results will rely on the way you keep text data inside your database. Should you store it as being LOB then behind the curtain it's saved within an regular file. With any type of LOB you have to pay the Database research + File IO anyway.
VARCHAR is saved within the tablespace
Regular text data types (VARCHAR et al) are extremely limited in dimensions in typical relational database systems. Something similar to 2000 or 4000 (Oracle) sometimes 8000 as well as 65536 figures. Some databases support lengthy text but these have serious drawbacks and are not recommended.
LOBs are references to file for system objects
In case your text is bigger make use of a LOB data type (e.g. CLOB in Oracle).
LOBs usually work such as this: The database stores merely a mention of the personal files system object. The file system object consists of the information (e.g. the written text data). This is much like what your friend proposes except the DBMS lifts the heavy work of controlling references and files.
The end result is: If you're able to store your text inside a VARCHAR go for this. If you cannot you've two options: Make use of a LOB or keep data inside a file recommended in the database. Both of them are technically similar and reduced than using VARCHAR.
The main advantage you will get from not while using filesystem would be that the database will manage concurrent access correctly. Let us say 2 processes have to customize the same text as the same time frame, synchronisation using the filesystem can lead to race conditions, whereas you'll have not a problem whatsoever with everyhing in database.
Used to do this before. Its chaos, you have to keep your filesystem and also the database synchronized constantly, to ensure that helps make the programming more difficult, while you would guess. Make an effort to either choose an exciting filesystem solution, or all database solution, with respect to the data. Particularly, should you require plenty of searches, conditional data retrieval, go for database, otherwise fs. Observe that database might not be enhanced for storage of huge binary files. Still, remember, if you are using both, youre gonna have to ensure that they're synchronized, also it does not alllow for a stylish nor enjoyble (to program) solution. Best of luck!
A minimum of, in case your problems range from "performance side", you could utilize a "no SQL" storage solution like Redis (via Ohm, for instance), or CouchDB...