MySQL query progress information [closed]

Question

I have a php script, that executes a mysql query. I have a HTML/JQuery frontend, which initiates an asynchronous call (AJAX) to a script requesting it to e.g. delete some data. I know my data pool is very small, but if it got bigger, I would like to display how many rows have been processed (because it is not possible to display the estimated time as it is not known until the query completes).

This question is rather theoretical than practical. Is there a way to know how many rows have been processed while the query is supposedly still running?

It can be a delete or a select statement.

My basic question is: is it possible to have one php script execute a long query while another one retrieves its status quo while it is still running whenever it is called? (e.g. somehow retrieve affected rows for a particular query or something?)

For simplicity reasons let's say I have 2 php files that I can call directly from my browser - one executes the query and another one supposedly retrieves its status. Is it possible and if so could someone point me into the right direction? Do I perhaps have to use some sort of stored procedures, that would at least report the amount of rows, that have been processed?

Answer 1

This question is rather theoretical than practical. Is there a way to know how many rows have been processed while the query is supposedly still running?

Not native, but you could work around.

is it possible to have one php script execute a long query while another one retrieves its status quo while it is still running whenever it is called? (e.g. somehow retrieve affected rows for a particular query or something?)

Those two scripts should share a common storage place in order to "communicate" and you can use Redis for that purpose.

The script who executes the query also updates the value from Redis and the other script reads the data from Redis and returns it to the browser.

The alternative will be to execute the queries in batches (of 10, 20, etc) and you can count the processed queries on the client side. In theory if the query script does not return an error, it means that all the queries executed successfully and you can increment the count value. You repeat the process until the query script returns a value of 0 executed queries, which means that you finished.

For more info see this answer on SO and http://redis.io/