Is it better to use mysql or files to store data?

Question

I'm wondering if it's better to use mysql or use files to store data? What is safer, what is faster? I'm talking about php and mysql.

Answer 1

Depends on the data.

Unless it is really trivial though, you may as well use mysql. There isn't really an obvious downside.

Answer 2

It completely depends on your requirements and setup.

For small amounts of data that doesn't require complex querying, files are faster. Site caches are one example.

For larger data sets or sets with more structure, a database will enable you to query your data in more advanced ways.

In terms of safety, files can be protected (using permissions) and stored outside the web root. Databases can be locked to certain hosts and can have users with different roles/permissions.

Answer 3

Generally speaking, you will want some sort of database or object store to store your data because it provides a clean interface to retrieve your data. However, it is very dependent upon the data that you are trying to store.

If you are making a website with user modifiable data, you probably want to use MySQL or some other database. If you are just talking about typing up some documents, the overhead of a database is probably more trouble than it's worth.

In order to provide a better answer, we need to know what type of data you are storing and how you want to access it.

Answer 4

As said, it depends on the data but also on your current bottleneck.
If your application is running on a server that is already short on IO resources, you might prefer in any case a network-type storage.

Security issues are more related to setup and good practices than to the technology which is used. If you write files, you need to make sure they are not downloadable. If you use a database, you need to take care of SQL injection and so on...

I'd just like to stress out that you are not restricted to files and relational Databases.

You can also use memcached to store data in memory (ideal for cache for instance). NOSQL databases like Redis are also an option.

• Database (ie MySQL):
  pros: searchable, can handle complex and structured data, can be hosted on a different sever, persistent
  cons: slow
  typical usage: business data storage
• Files:
  pros: very easy to setup, quite fast, persistent
  cons: not suitable to handle very complex data, not searchable, will consume server host IO resources, not that fast
  typical usage: log files, template cache files
• NOSQL (ie Redis):
  pros: fast, can be setup on a remote host, persistent
  cons: not easily searchable, not suitable for strongly structured data
  typical usage: performance hacks (search)
• Memory:
  pros: The fastest one!, can be setup on a remote host cons: same as NOSQL + not persistent
  typical usage: object caching