Shawn,
I like your idea very much. Unfortunately I am not using version 5 yet, but this may hasten my upgrade.
My initial thought was to try and find a function like PostgreSQL's LISTEN command (http://www.postgresql.org/docs/8.0/interactive/sql-listen.html). But I think this method is better.
As an extension to your idea, my UDF function can broadcast messages using the RSS protocol. I am sure there are some easy to use libs out there. My listening thread(s) can feed off the RSS listening for trigger events.
This negates the use of a polling thread, and indeed the entire thread. I don't need a connection to the database at all until I know there is data there. Magic :)
Thanks,
Ben.
[EMAIL PROTECTED] wrote:
Ben Clewett <[EMAIL PROTECTED]> 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
