Google Answers Logo
View Question
Q: (MySQL V.S. Flat file) & (MySQL Optimization) ( No Answer,   1 Comment )
Subject: (MySQL V.S. Flat file) & (MySQL Optimization)
Category: Computers > Programming
Asked by: royteo-ga
List Price: $10.00
Posted: 07 Oct 2006 15:20 PDT
Expires: 06 Nov 2006 14:20 PST
Question ID: 771589
Hi guys,

I am developing a chat application based on PHP + MySQL.
Basically I have 2 way to go about polling and storing data.
I am leaning towards MySQL right now because of the convenience, what
Im concerned with is the resource consumption.

1. MySQL Database
If I were to opt for MySQL, I will have my connection "Keep-Alive"
style as opposed to the easier but resource intensive
poll-every-second-ajax method.
As the connection is only opened and closed once (due to "keep-alive"
connection), the only thing I need to worry about is the resource
consumption of the queries themselves.

2. Flat file
This option seems to be the way to go if I want to minimize resource
consumption. The only caveat is the hassle, inconvenience and its
harder to manage.

I wanted to know:
1. Which option should I go for?
2. Do you have some further optimization tips that my chat application
can benefit from? e.g. denormalization, MyISAM/InnoDB, etc
3. From your experience, do you think there is a stark difference
between the resource consumption of the 2 methods?
There is no answer at this time.

Subject: Re: (MySQL V.S. Flat file) & (MySQL Optimization)
From: frankcorrao-ga on 09 Oct 2006 13:21 PDT
It sounds like your design is to have each client update the mysql db
or flat file when text is entered.  The clients would then
periodically query the db or read the file to check for updates.

The advantage of this design is simplicity.  The main disadvantage is
scalability.  You are rigth that maining a connection does improve
performance, but ultimately you still have n clients polling a
database.  Contrast this again a chat server design that where each
client connects to the server, who maintains the chat state for each
client.  When a client sends a new messages, the server pushes down
the messages to all the other clients that are part of the current
chat session.  The server could also have a thread that maintains a
connection to a database (or file) to keep the chat state stored. 
This is considerably more complicated to implement.

As to the question of whether to use a flat file vs a database in the
original design, I would probably go with the database.  You are right
that a flat file is much harder to manage.  You need to implement the
locking semantics on your own.  Mysql is a pretty simple database. I
don't think the performace will be much worse than a file.  I would
say that a good rule of thumb is not avoid premature optimization, or
as Knuth might say "Premature optimization is the root of all evil".

What you should do is try to encapsulate the chat state code as much
as possible so that if you do find you need to swap out the db code
for flat file management, you can do without having to modify any
other code.  The simplest way to do this is with inheritence.  An
abstract class defines the interface.  The rest of the code knows only
about the interface, not the implementations.  If you switch from a db
implementation to a file implementations, the rest of the code is
unaffected because all of that is hidden from it.  A better way to do
it is to use the bridge design pattern, but that is probably overkill
unless you just want to do it for the exercise.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy