MySQL – Get Last Query Cost Using SHOW STATUS LIKE ‘Last_Query_Cost’

During the recent webcast I did for Idera, I had the pleasure of showing quite a lot of new tricks and tips. One of the most appreciated tips was about how to get Last Query Cost in MySQL using the command SHOW STATUS LIKE ‘Last_Query_Cost’. Let us learn about it today.

If you are using SQL Server, it is very easy to get the cost of the query by using either a graphical execution plan, XL execution plan or using plan cache DMV. However, if you are using MySQL, it is very difficult to figure out the last query cost. I have often seen during my Comprehensive Database Performance Health Check that DBAs want to tune their MySQL queries but they have no idea how to measure the performance of their queries.

If you are using MySQL, or MariaDB, you can always measure the performance of your query in terms of the cost by running the following command.

Show Status

SHOW STATUS LIKE 'Last_Query_Cost';

Here is the official documentation for the command. 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.

Let us see a simple example of how it works with the help of the Sakila sample database for MySQL.

USE sakila;
SELECT *
FROM film f
INNER JOIN film_actor fa ON f.film_id = fa.film_id
INNER JOIN film_category fc ON fc.film_id = fa.film_id
WHERE f.film_id = 10;
SHOW STATUS LIKE 'Last_Query_Cost';

Here is the outcome you get when you run the above query:

With the help of the status, we can know what was the cost involved in running query. If you have any other suggestions do let me know. Additionally, If you want to Monitor in real-time for corrective action and issues resolution, you should consider SQL Diagnostic Manager for MySQL and MariaDB.

Reference: Pinal Dave (https://blog.sqlauthority.com)