The most traditional way of predicting the cost of a query in MySQL is through the EXPLAIN command. And, it helps a lot. No one should put a query into production without first parsing the output of an EXPLAIN command.
Another, not very traditional way of analyzing the behavior of a query is through its profile (SHOW PROFILE).
In MySQL 8.0 a query cost metric was introduced. It is a state variable (LAST_QUERY_COST), which indicates the cost of the last query in the session. It’s not a magic number. It doesn’t solve everything. But it certainly serves as a flashlight for a dark tunnel. It can and should be combined with EXPLAIN and SHOW PROFILE. Or, used alone, for quick analysis.
To view the cost of a query, simply: 1/ Execute a query (SELECT), 2/ Show Status Like ‘Last_Query_Cost‘. 0 (zero) will be returned if no query has been executed in the present session, or a positive number. The higher the number, the worse the cost of the last query executed in the current session.
I will use the star wars database I created for presentations and training. Let’s play with two tables, and, simple queries. The objective is to demonstrate the use of Last_Query_Cost.
Table 1: Transport Log. This table records all interplanetary flights on Federation routes. It contains almost 16 million registered flights (lines).
Table 2: Planets. A table containing the planets covered by Federation routes. With only 61 planets (lines).
THE PROBLEM WITH THE QUERY
Senator Palpatine would like to know how many flights took place from Alderaan to Cato Neimoidia, the purpose of the flight, and how many living beings were on board.
The demand translated into SQL:
SELECT logID, pfrom.planet_name, pto.planet_name, flightTYPE, pob species_on_board
FROM transportation_log, planet pfrom, planet pto
WHERE pfrom.planetID = planetfromID AND pto.planetID = plantoID.planetID AND Planetfromid = 1 AND plantoID = 5;
In total, 6,298 flights (lines) will be found. The query took 0.11 seconds. And, there is no kind of index on this table.
Okay, let’s go to SQL commands and use the LAST_QUERY_ID.
Before executing Senator Palpatine’s demand, let’s play with a much simpler query.
A simple query, on a small table (61 rows). It gave us a cost of only 6,349. Keep this number, as it will serve as a comparison guide for our study. And may the force be with you!
I ran the query with LIMIT 10 to have the result and SQL visible. But LIMIT will not change the purpose of our study.
We got a scare when requesting the cost of the last query through the command SHOW STATUS LIKE ‘Last_Query_Cost’.
The cost of the consultation was no less than 5,381,145,018.726037. Was there any interference from the Force? A cost of 5 billion is a lot of cost for little SQL. There is a logic to understanding this number, but for this article; just accept that the bigger… the worse.
Let’s understand how the MySQL 8.0 compiler would execute this query:
Notice that there is no index, evidenced by the column Possible Keys = NULL. Of course, this type of query on tables without indexes would be a disaster.
Let’s improve this, creating some indexes, and evaluating if there was an improvement in the cost.
Wow! The query cost dropped from 5 billion to 223,494.664053. On a quick division account, we can assume that the weight of this query has become 24,000 times lighter. It’s a lot of improvement.
The cost of the consultation is not only translated into time. But, in the use of server resources, such as memory, CPU, and, disk I/O. The lower the cost of the query, the faster and fewer resources will be taken.
To improve this query I created an index with the planetfromID and plantoID columns (source and destination planets), through the command:
CREATE INDEX idx_planet_ALL ON transportation_log(planetfromID, planettoID);
The EXPLAIN of this query looks like this.
THE CONCLUSION
Last_Query_Cost is a valuable state variable to tell us the weight of a query. And, if combined with EXPLAIN (and, eventually, with SHOW PROFILE) it gives us a good analysis platform to improve query performance.
Learn more about MySQL and our Database Monitoring Solution on our blog!
