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.

MySQL - Get Last Query Cost Using SHOW STATUS LIKE 'Last_Query_Cost' showstatus-800x194

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:

MySQL - Get Last Query Cost Using SHOW STATUS LIKE 'Last_Query_Cost' lastquerycost

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)

Execution Plan, MySQL
Previous Post
Choosing a Processor for SQL Server
Next Post
SQL SERVER – Drivers for DevOps – Efficient Monitoring

Related Posts

1 Comment. Leave new

  • NySQL until 8.0.16 reports last_query_vost as 0 for any query having a sub-query clause.

    Reply

Leave a Reply