How to find out when an SQL query will end?

Question

Is there any way to find out when the SQL query will complete after it is run? In other words, is there a way to find out how long the query will take to complete before the query is executed? (a rough estimate is perfect!)

ALTER IGNORE TABLE mytbl ADD UNIQUE (c); 

I ran this query in an innodb table containing 35 million records. c is varchar (255)

This command has been running for 10 hours and it is still running. I do not want to cancel if I am near the end.

I appreciate any help. Thanks!

Answer 1

Excerpt from ServerFault :

The solution was to determine how quickly the query scanned the rows in the fact table scan. This is displayed by the status variable Handler_read_rnd_next. Heres an easy way to watch it (innotop is another convenient way):

 mysqladmin extended -r -i 10 | grep Handler_read_rnd_next -- ignore the first line of output... | Handler_read_rnd_next | 429224 | 

Thus, the server read about 43 thousand rows per second, and the table had 150 million rows. A little math later, and you will get 3488 seconds to complete, or a little less than an hour. Indeed, the request is completed in about 55 minutes.

Answer 2

The big problem is that the most important factor (of course) in determining how long a request will take has nothing to do with the request itself. Rather, the most important factor in determining the response is the load placed in the database during the execution of the request, especially including loading from other requests. For complex queries in active systems, this load can change significantly during the query.

What you can do is get the execution plan for your request using the EXPLAIN keyword. You can use this to get a relative idea, all other things being equal, for how much a particular request can cost. Even here, however, MySql does not make it easy, as it will not just give you good tariff plans or run-time numbers that you can use, as the sql server does. You should output these numbers based on strings and disk reads.

Again, even if you generate an estimated cost and then run the request, you can get the results for a longer or shorter time than expected because the server load changes after you create the estimate and execute the request.

---

In this case, after 10 hours, it looks like you might have a lock / lock problem. If you have another active (possibly long-term or frequently repeated) query that uses this table, try killing it, and there is a good chance that your query will complete naturally in a few minutes.

Answer 3

Use test database, smaller data set.
This will give a very rough estimate. But I believe that in your case time will be non-linear, for example: exponential. Perhaps you can remake the team so that you guarantee a unique c.