Ben Clewett <Ben.Clewett@stripped> wrote on 03/21/2005 11:36:45
AM:
> Dear MySQL,
>
> I have an application which need to wait for some data to change, then
> act on this change.
>
> I am polling the MySQL once a second using "SELECT ..."
>
> I believe there is an alternate method where a thread can be made to
> wait for some change, therefore avoiding the expensive polling and
> improving performance.
>
> If any member knows what this may be, I would be very interested.
>
> Regards, Ben Clewett.
>
I can't think of any way to do this "efficiently" without a trigger, a
UDF, and a custom daemon. I assume based on your description that you have
some sort of "client" application that needs to know if some data value
has changed. However, you don't want to keep "pinging" the server to
continuously ask "has it changed yet, has it changed yet, has it changed
yet..."
Here's how I think you can make this work with some sort of efficiency.
You need to setup your application to connect to a daemon on some server
somewhere. The only purpose of this daemon is to broadcast to those
clients who connect to it a message to the effect that "some data has
changed on the table you are interested in". If you wanted to get really
fancy, the message could say what data changed and possibly what the new
value is. That way your client could possibly act on the data without
needing to poll the server.
How does the daemon know that data just changed? That's where the trigger
and UDF come in. Triggers are fired during certain database events (adding
records, changing records, or deleting records). I don't know for certain
but I believe triggers can use UDFs in their code. The UDF that is called
by the trigger detecting a change in the table you are interested in is
what notifies the daemon (described earlier) that the data has changed.
And, because the UDF is only called from within a trigger, you only bother
the daemon when a change actually occurs.
The downside to this design is that triggers are not supported until 5.x
(still in testing) so I don't know if you have that option. User-defined
functions (UDFs) may be able to cover the job by themselves IF and only if
you can control data changes to ALWAYS use your UDF. Otherwise some
changes may slip through the cracks.
Anyway, that was my idea. Is there any other way for you to communicate a
change in data than a "passive detect" like you are doing? How much
control do you have over what can and cannot change your data
(specifically the field you keep polling)? What other application-based
options are open to you?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
