Page 1 of 1

MySQL or Flat Files

Posted: Tue Apr 20, 2010 3:00 pm
by elTavo2
I am developing a website in PHP that requires constant information updates (every 10 seconds) from a database. This information is temporary. For this, I used AJAX. (Think of it as a chat app.)

Before I go any further in my project I was wondering if it is wise to implement MySQL as a database or flat files. I have read a lot on this issue but all point out the security problems flat files have and that they are slower compared to MySQL (among other things).

In my case, the file size would be very small and it is not sensitive data. In any event, I could md5 the whole thing if necessary.

My main concern is that, being that there would be a lot of queries to MySQL (in case I choose that form of database), I would drastically harm the site and overall server performance.

So I was wondering if it would be better to handle this information in temporary flat files. Any imputs?

Re: MySQL or Flat Files

Posted: Tue Apr 20, 2010 3:03 pm
by Benjamin
You could use memcached if the data does not need to be persistent. Essentially, you would simply be storing the data in ram.

MD5 is only a one way hash, so that will not work in your case. You could encrypt it, but the encryption keys would be easy to find.

MySQL also offers the MEMORY table type, which is table simply stored in ram.

Re: MySQL or Flat Files

Posted: Tue Apr 20, 2010 5:02 pm
by Christopher
elTavo2 wrote:My main concern is that, being that there would be a lot of queries to MySQL (in case I choose that form of database), I would drastically harm the site and overall server performance.
Are you sure this will happen or just conjecturing? You should build it with a straightforward design first, then optimize. For example, MySQL has a query cache ...

Re: MySQL or Flat Files

Posted: Tue Apr 20, 2010 5:20 pm
by pickle
Will there be multiple writes - ex: many connected users can write to the file/database, OR
One source of writing - ex: you have a cron job that runs every 10 seconds & writes to the database/file and each connected user just reads the data?

If you're doing the former, use a database (with whatever engine or technique you wish), if the latter use a file. For non-persistent data, a file is probably best, but databases deal with concurrency issues so nicely, it's best to use them if you have multiple writing sources.

Re: MySQL or Flat Files

Posted: Tue Apr 20, 2010 5:25 pm
by Eran
MySQL has many provisioning for improving performance, such as using buffer pools and indexes - it's highly unlikely you could come with something more performant using flat files. Not to mention contention and integrity issues. The only thing for flat files is simplicity, but that usually is offset by the previous drawbacks.

The only thing you might store in temporary files (ie, file cache) are very complex queries results that might be too expensive to run repetitively. Either way you should profile your queries before deciding to cache the results.

Re: MySQL or Flat Files

Posted: Tue Apr 20, 2010 8:37 pm
by elTavo2
Thank you all. I think I´ll give MySQL a chance and see how it works out.

Re: MySQL or Flat Files

Posted: Thu Apr 22, 2010 3:39 am
by Chris Corbyn
Could you elaborate more on the data you'll be storing? If it's only temporary and it doesn't need to be accessed by multiple users then you could even use the session for this.

I'd avoid text files. You'll have more headaches trying to make things work. As your requirements expand you'll be grateful if you have a database schema to refactor as opposed to a bunch of text files with some arbitrary format.

Re: MySQL or Flat Files

Posted: Thu Apr 22, 2010 8:32 am
by elTavo2
Though it is not a chat application, think of it as such.

There are multiple user in a lobby and a particular user can create a private chat room (2-8 users max). The information regarding these private chat rooms would be store in a database (users, messages). When the host of the chat room exits, the room would be closed and erased.

So:
• Users in the lobby have to query a database constantly to check for private rooms.
• Once inside a private room, the users in it have to query another table for messages.

The private rooms would be temporary.

Re: MySQL or Flat Files

Posted: Thu Apr 22, 2010 9:59 am
by Chris Corbyn
I'd definitely use a database then, but also take a look at memcached.