How to determine how long a database query will run?

Question

Performance evaluation

The favorite question of the authorities is “how much will it be considered?”. The database is large, the queries are complex and different all the time (in the sense they are generally different, and not with different arguments). How can you determine the future execution time of queries in this case?

Answer 1

Run a query on a limited number of records, and then extrapolate to the available number of records?

Answer 2

In general, no way.

Answer 3

I would advise you to understand why the authorities are still asking these questions.
I have multiple versions.
The bosses have no idea how the development process is going, but they want to show their importance. Since this is a rhetorical question that you always find it difficult to answer, this is how they assert themselves, trying to lower your assessment as an employee. We single out three main problems
1. The authorities do not have enough information to make a decision.
You need to explain in more detail why you cannot answer the question posed. Since we don't collect statistics on query execution time, we should suggest spending a little time refactoring the code and start collecting data. After a while, you will be able to answer almost any of the questions posed. This simple move will show your ability to solve supposedly unsolvable problems and satisfy the information hunger of your superiors.
2. The authorities are trying to assert themselves at your expense. Pay no attention to it. Be above it.
3. The bosses are trying to humiliate you and this is a real cause for concern. Make a knight's move as described in paragraph 1, this will remove one of the main trump cards of pressure - your failure to answer the question. If your proposal is not accepted, then this will give you a good answer to the question: “You didn’t want to collect statistics, now what questions do you have for me?”
Another version: the authorities are trying to understand what is wrong in development, what needs to be fixed to make it better. Or trying to figure out how to monetize competitive advantages.
True intentions are easy to determine by asking why you are being asked such a question. Don't be shy, come on.

Answer 4

I would just conduct practical tests, if after them the time turned out to be unacceptably long, then you can split the request into sub-requests, then execute each sub-request separately and watch the time, thereby finding a weak spot.

Answer 5

There is no such thing in MySQL. It is easier to evaluate yourself based on experience and the amount of data. You can see the output of EXPLAIN on the optimization score.

Answer 6

Yep, count the time. What's the point?
And when such requests will be simultaneously executed 10-20, and 100?
I also somehow thought, 2-3 requests - everything flies, released into production - there are 30-50 in parallel - everything became a stake. Until the operatives were added to the server and the database was tuned ...

Answer 7

Remember the time before, after and show the difference? Or is it necessary to calculate without starting the process?

Answer 8

Is it possible to see the query plan in MySQL? According to the plan, you can roughly understand what nature of the dependence of the execution time on the number of records

Answer 9

Please send someone an invite, I really really want to look at this miracle-yudu.
Thanks in advance.