From: Rick (Richard Lee-Morlang)
Date: March 17 1999 10:48am
Subject: Re: beeing informed of changes in rows in a table (real-time prices)
List-Archive: http://lists.mysql.com/mysql/398
Message-Id: <36EF8884.3C8ED314@webtownis.bc.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Simon J Mudd wrote:

> I think triggers can be used to perform calculations/functions on the data
> in the database (as inserts/updates take place) which would solve most of
> my problems, but I still need the "client" to know when the data changes.
> If I want to cope with "real-time" (sub 1 second) updates for a number of
> clients frequent SELECTS will presumably cause a severe load on the
> server, which I'd prefer to avoid.
>
> For the time being I can afford to "poll" every 10-30 seconds.

"Severe load" depends on the magnitude of what you're doing.  I could see 50
clients doing a select every second against a database be quite feasible,
provided that the selects weren't too computationally expensive and the
datasets being returned weren't too large. "too" is obviously a relative
term, relative to a number of other things. :-) But something like this isn't
unrealistic. Unless you have some more concrete numbers as to what you
require, you could try simulating that kind of load on the server and see how
it holds up.

As suggested by Ed Carp, using a process on the server to push data out to
the clients makes a lot more sense, of course. And if you were using a method
like this and needed near-realtime responses, you could turn on update
logging in MySQL and have your push process watch the logfile for changes.
Every time it sees a change, it issues a query to grab updated data and
pushes anything it finds out to the clients.

I don't know if that qualifies as a dirty hack or not, but it would work, and
I've done some similar things myself in the past with a lot of success.


Rick