On Tue, Sep 13, 2011 at 11:44 AM, Jim Lucas <li...@cmsws.com> wrote: > On 9/12/2011 7:40 AM, Marco Lanzotti wrote: > > Hi all, I'm new in the list and I already have a question for you. > > I'm running an heavy query on my DB in a PHP script called by AJAX. > > Because client often abort AJAX connection to ask a new query, I need to > > stop query because DB will be too loaded. > > When AJAX connection is aborted, PHP script doesn't stop until it send > > some output to client, so I need to wait query execution to know client > > aborted connection. > > How can I abort query (or script) when AJAX connection is aborted? > > > > Thank you, > > Marco > > > > > > You cannot stop a DB query. > > What this means is PHP will not be able to do anything else until the db > has > finished its step and handed data back to the processing script. At that > point, > you can check to see if the connection is still active and take appropriate > action. > > Jim Lucas > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Correction on Marco's post. You can absolutely stop a mysql query, it is done with a large amount of success at Facebook for example, where they have very strict query execution rules, e.g. if your query takes too long to run, it is killed. However unless you are dealing with enormous data sets, or very very slow mysql server, this is not worth the tremendous amount of trouble you would have to go through. And if you are dealing with enormous data sets or slow servers, it would be far more beneficial to address those issue then to implement the query killing thing.
MySQL commands in question are: SHOW PROCESSLIST; KILL [thread]; You can also hook into if you really wanted to with some C through the API, but again, it is far more trouble than most people need, and problems often lay else-where (for example inefficient query or bad database design or matching on non-indexed cols etc...) A query that ties together 3 tables and pulls 80-90k rows @10 columns shouldn't take more than 0.25 sec to execute, maybe a second for the whole operation from connect to result, if your mysql server is one hop away (i.e. they are on the same switch), the tcp hand shake can take up to 100ms, plus you need to get the process list, traverse it for your query, and send a kill command. I'm going to guess that the kill process will take longer to connect, list, parse and kill, then it will take the query to finish and return data... What is your data set like, what are you trying to accomplish by this other than complicating your code? Also yes, AJAX is your friend (avoid pulling large or any data sets if you can), as well as some query and database optimization, and caching ;) -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray