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.

Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography.

I'll admit my SQL queries could be better but in general what would be faster and less processor.

To check if a file exists and then open it or

check against MySql to see if a bio exists and display it.

I'm pretty sure in the above case the filesystem will smoke the mysql database.

What if I make the database a read only delimited txt file?

What's faster in this case?

Is there a certain point where if the txt file has too many records it's better to use MySql?

share|improve this question
2  
Lets say you have 100K people in your directory and you want the bios of those that were born in 1978. Where do you think the smoke will come from? Opening 100K files in the file system or a single query in SQL? –  ypercube Aug 25 '12 at 9:28
1  
@ypercube - I agree with you but in case of linux OS there is a limit for opened files simultaneously with each processor. –  Satish Pandey Aug 25 '12 at 12:02
add comment

4 Answers

up vote 10 down vote accepted

The file system is useful if you are looking for a particular file, as operating systems maintain a sort of index. However, the contents of a txt file won't be indexed, which is one of the main advantages of a database. Another is understanding the relational model, so that data doesn't need to be repeated over and over. Another is understanding types. If you have a txt file, you'll need to parse numbers, dates, etc.

So - the file system might work for you in some cases, but certainly not all.

share|improve this answer
    
+1, also file systems are not good for partial searches on file names or other attributes. When the number of files is so large you may have an issue finding files this way. Having said that it is common to use file system for data that is not transactional in nature and where the content is always accessed as one unit, such as document attachments and image files. –  Emmad Kareem Aug 25 '12 at 11:30
add comment

It really depends on what you are doing. In general the speed at which you can open a file for reading will be better than the speed at which you can establish a network connection. So for very simple operations, the filesystem is definitely faster. Filesystems will probably beat an RDBMS for raw read throughput too since there is less overhead. In fact, if you think about it, the database can never be faster than the filesystem it sits on in terms of raw throughput.

For very complex operations, the filesystem is likely to be very slow. For example:

Read 10 lines out of this 1 billion line file and then search for matching lines in this other file. I pity you if you have to do this. A good database server however has strategies for doing this fast and well so you aren't reinventing the wheel.

Additionally you really need to figure out what you are doing. What data are you storing? How are you going to transform it? If it is 100k image files your solution will look very different than if it is a directory for 100k people. (LDAP maybe? Or an SQL database? Depends on what you are doing, perhaps.) The key here is to pick the tools that match what you are doing and which give you room to add more uses, rather than whatever seems fastest for some rather abstract use case. Databases are wonderful tools, but you can't get a good answer to a question like this.

Finally premature optimization is the root of all evil. Choose useful tools now and figure out the rest later.

share|improve this answer
add comment

The file system might be faster initially, but I doubt it. However, as your data size increases you will likely have to restructure your file system to maintain performance. Besides their obvious ability to index on multiple attributes, databases tend to scale better.

Web caches which work similarly to what you are considering use directory tree to maintain performance. They also tend to be of a relatively fixed scale, so they don't have to deal with a growing scale.

For this kind of application I would start with a database, as it fits better with your needs. It will scale much better in the long run. Compared to most file systems, a database will also be more space efficient.

share|improve this answer
2  
Well, that's not a problem. Let's just create another file that lists values and seek offsets. In fact we could optimize this for searching with btrees. Then we know where to read the file! Next up, I suppose we should add a declarative query language to our little program capable of joining results between different delimited files and then maybe ACID compliance.... In time, well, why use an RDBMS at all? ;-) –  Chris Travers Aug 31 '12 at 13:03
    
@ChrisTravers Been there, done that, and I am much happier using a database. –  BillThor Sep 2 '12 at 3:19
3  
the idea was along the lines of "Those who do not learn from UNIX are destined to reinvent it badly." –  Chris Travers Sep 2 '12 at 3:23
add comment

I always love coming to these forums and reading all the heavy database gurus pitching that the file System can't do it as quick as the Database. On quite the contrary a properly layed out tree, well designed hashtables and saving them as an object to a file will yield the same speeds as a database and from my tests. A properly designed hashtable and directory tree will win everytime. Way less overhead. Recently I have been moving away from database driven programming and more on the file tree for simplicity and program portability. No DB means easy backup just zip your tree up and go. It is very nice and a recomendation to program in this fashion for onetime clients with small applications. Look at the big pic do I have the time to design my own or just leverage what is already there like the db. I personally like saving my objects to file and using them later just keep an eye on your the size of your tables and look into using a RandomAccessFile in order to be able to seek quickly lay it out like a database and break it up into hashtable objects. Enjoy. Remember what ever data you store in the file will consume double the memmory usage at times depending on your code. The hash table itself and typically where you consume it to view.

share|improve this answer
2  
The only appropriate response to this I can think of is this. –  Mark Storey-Smith Mar 8 '13 at 21:56
    
@MarkStorey-Smith, that's an interesting link, but is it presumptuous to imply this solution as being on the Dunning-Kruger spectrum somewhere? :) –  David Mann Aug 29 '13 at 13:51
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.