Further information about last_query_cost in MySQL?


Further information about last_query_cost in MySQL?



When doing query optimization, the SHOW STATUS query returns values that are easy to understand with some practice and explanation.

But last_query_cost is obscure and poorly documented.

The only thing explained is that it must be read as an anti-macho value: the smaller the better.

But do we have further information about this high-level value? What is its unit? How is it calculated (estimated)? etc. How can we use it for an advanced profiling?

Thanks for your precious help. :)




Is a relational database well suited for vector calculations?

1:



Branching optimization
This has to do with how the MySQL Query Optimizer works.


Deserializing data from file. Performance issue
When you enter and execute a query, MySQL will construct a query plan.


Are loops really faster in reverse?
This is done by evaluating how the query can be executed in several different ways, and assigning "costs" to the different possibilities.


-fno-omit-frame-pointer without optimization
These costs are based mostly on internal statistics, and includes data such as the number of rows in the table, the cardinality of different indices and so forth.


optimization and debugging options in Makefile
When this is done, MySQL choses the least expensive plan and executes the query.


High-level/semantic optimization
The last_query_cost value is this cost value..
C++ stack and scope
As you've no doubt seen in the manual:.
The total cost of the last compiled query as computed by the query optimizer.

This is useful for comparing the cost of different query plans for the same query.

The default value of 0 means that no query has been compiled yet.

The default value is 0.

Last_query_cost has session scope..
This is indeed true.

The value is only useful as a quantitative measurement to compare different queries.. There's some interesting resources on the query optimizer available online, if you want to learn more.

Unfortunately, I don't have any links for you readily available, but it shouldn't be too hard to find some resources through a simple search for "mysql query optimizer"..


2:


This post makes it sound like it may simply be an estimation and therefore not something you should rely on for advanced profiling:.
One thing I didn't show you about those two queries was the value of Last_query_cost.

That's because it showed the slower, more data-intensive query actually having a lower cost than the faster one: Last Query Cost in Table Redesign.
Variable_name    Design1       Design2 Last_query_cost  20343.599000  71039.632551 
That's pretty bizarre, isn't it? I don't know how the query cost is calculated; I believe the optimizer calculates it in advance of actually executing the query.

It definitely doesn't match the actual cost of executing these queries.

It is usually more in line with the true cost, but not always.

You should not rely on it absolutely..



76 out of 100 based on 66 user ratings 766 reviews

*