MySQL or Flat Files

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
elTavo2
Forum Newbie
Posts: 3
Joined: Tue Apr 20, 2010 2:41 pm

MySQL or Flat Files

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL or Flat Files

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL or Flat Files

Post 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 ...
(#10850)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MySQL or Flat Files

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL or Flat Files

Post 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.
elTavo2
Forum Newbie
Posts: 3
Joined: Tue Apr 20, 2010 2:41 pm

Re: MySQL or Flat Files

Post by elTavo2 »

Thank you all. I think I´ll give MySQL a chance and see how it works out.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: MySQL or Flat Files

Post 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.
elTavo2
Forum Newbie
Posts: 3
Joined: Tue Apr 20, 2010 2:41 pm

Re: MySQL or Flat Files

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: MySQL or Flat Files

Post by Chris Corbyn »

I'd definitely use a database then, but also take a look at memcached.
Post Reply