What's better/faster? MySql or FileSystem?

not talking about images and blobs

[MrSpeed]

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?

[spinnercee]

The simple answer is a no brainer...

If you try to use a single TXT file as a database it will be less efficient [slower] than a database unless you return the whole thing -- as soon as you have to scan the TXT file to extract a part of it, a database becomes a better idea.

You're also correct that returning individual files from the file system is faster than returning them from a database reference because the filesystem [DIR, FAT, etc...] by definition is a databse that is optimized for rapid random retrieval especially when augmented by the filesystem cache, and the database is really a set of files anyway with a much finer set of extraction points that can return objects smaller than a whole file.

An argument can probably be made that a database could be better if the host filesystem is limited in its ability to return whole files (or keep data sufficiently cached) in one way or another -- for example, the FAT filesystem is not nearly as optimal as the NTFS file system, which may not be as well optimized as a Netware filesystem, etc... it depends on the environment.

From a performance standpoint, I would consider the overhead of the scripting language and database app over the HTTPd simply getting files from the filesystem -- scripts like PHP are always parsed, interpreted and executed on every invocation, and a TCP request is created and made to the mySQL server to retrieve data, whereas an HTTP GET request for a file is passed directly to the OS. In that case the database retrieval is non-trivial compared to the simple HTTP retrieval.

[eelixduppy]

...or if you just cannot decide then try it out for yourself and see what the results are :) As it was already stated, it will depend on the environment and certain other variables.

[mcibor]

A I recall there is a number of files in a directory up to which filesystem is faster than sql - it's about 100.

However if you start to perform search query, then a database is faster than 30 files.

So if you have less than 100 employees, and you don't want to search them - use filesystem, however if you want to list them, use sql.

Regards
Michal

PS. Function microtime is good to make a performance tests.