Im trying to think of the best way to handle real time search for a website. The only solution I can come up with is to keep checking the server every few seconds for something new in the database, but that does not seem very practical to me.
views:
293answers:
6
A:
No, it cannot, because MySQL cannot. You need to use other technologies, possibly including a message queue, or a streaming database.
Yann Ramin
2010-05-21 18:14:34
A:
AFAIK, there's no way to tell MySQL to ping your script when something happens there.
Polling seems to be the only plausible solution...
Seb
2010-05-21 18:15:48
do you think thats how twitter has done it?
Derrick
2010-05-22 08:27:24
@Derrick WTF? There's plenty of solutions for Twitter. You don't need the DB notifying anyone of an update; it's probably the very same script posting the new status update the one in charge of forking a new thread to notify other parties.
Seb
2010-05-22 16:24:52
yes - but this is not a twitter application.
Derrick
2010-05-22 20:24:26
Then why you come with the Twitter question at all? Man, what you asked is not your real problem; you'll never get the answer you want. You should choose one of these answers as correct, because most of them are answering your question - though not your needs because you expressed them wrongly.
Seb
2010-05-23 19:21:14
@Seb: When using the Twitter web interface and you're in search or your main timeline, when there's a new tweet, Twitter tells you there's a new tweet and lets you refresh the timeline for the new tweets. The question was not related to the Twitter API at all. Twitter was just used as an example website that does the behavior the OP wanted.
icktoofay
2010-08-27 01:38:53
A:
I have not played with it but have noticed that the Symfony framework has introduced "event notification"... basically, every time one part of the app makes an INSERT or UPDATE, it creates an event, and other pieces of code can register to "listen" to events and fire.
That would be all at the PHP level, not the database level, but is I think what you're looking for.
There's a good article about symfony events here: http://www.symfony-project.org/blog/2009/02/21/using-the-symfony-event-system
Nathan
2010-05-21 18:17:21
of what use is that after you have sent the page to the user?does this event system ping the user and tell him to reload the site?
2010-05-21 18:27:57
No, for getting the information to the user you would have to poll from the client side. The events allow other server-side PHP code to run based on MySQL database changes though, which was the original question.
Nathan
2010-05-21 18:34:12
I interpreted "real time search for a website" as website that auto queries mysql every 5(or whatever)secs for new results and then paste that data into the already existing list of results
2010-05-21 18:47:09
Im thinking twitter home page here: http://twitter.com/ the type of thing trying to do.
Derrick
2010-05-22 08:26:59
A:
even if php could how would u show the updates to the user, u will have to query the server to show the results.
and the only way is what you are already thinking of, querying the server every 5 or more seconds,.,,.
ovais.tariq
2010-05-21 20:20:53
+1
A:
As of 5.0.2 MySQL supports triggers: http://dev.mysql.com/doc/refman/5.0/en/triggers.html
But unfortunately I don't know MySQL well enough to know whether you could do anything "useful" in them to notify the app (in Oracle I'd be thinking xp_'s). At very least you could write a row into a single, audit-like table so that you only have to poll in one place.
pdbartlett
2010-05-21 20:42:10
Cool that sounds like a step in the rite direction, the next would be to tell mysql to run a php script and then run js on the web browser to update the page, I cant understand how twitter has done it...?
Derrick
2010-05-22 08:26:09
A:
You could use something like Gearman (which has a MySQL interface) to prod your php code when something new happens. Maybe you could use those events to build a static page with all the newest info, and use polling on the client side to grab that static page.
I've yet to use Gearman at all, though, so the details on such a setup are beyond me.
nilamo
2010-08-27 01:40:06