You could make your polling query as light weight as possible. Create a new table with a single integer. When an update happens increment that integer. Your polling query will then just be a select from that table to see if the number has been incremented from the last time it performed the operation. You can have several threads polling this table server times per second without any noticable performance impact (especially with the query cache).
-Eric On Mon, 21 Mar 2005 11:57:00 -0500, [EMAIL PROTECTED] <[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 > > -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
