Bookmark and Share
MySQL query optimizer cost -- Last_query_cost
(Publish Date: 2009-8-20 12:55pm, Total Visits: 6494, Today: 2, This Week: 13, This Month: 46)

Last_query_cost: 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.

MySQL uses a cost-based optimizer, which means it tries to predict the cost of various
execution plans and choose the least expensive. The unit of cost is a single random
four-kilobyte data page read. You can see how expensive the optimizer
estimated a query to be by running the query, then inspecting the Last_query_cost
session variable:

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
| count(*) |
| 5462 |
mysql> SHOW STATUS LIKE 'last_query_cost';
| Variable_name | Value |
| Last_query_cost | 1040.599000 |

This result means that the optimizer estimated it would need to do about 1,040 random
data page reads to execute the query.

It bases the estimate on statistics: the number of pages per table or index,
the cardinality (number of distinct values) of indexes, the length of rows
and keys, and key distribution. The optimizer does not include the effects
of any type of caching in its estimates—it assumes every read will
result in a disk I/O operation.

When we optimize a query, we can execute the two queries and look at the
Last_query_cost variable for each. The query with small "Last_query_cost" is an
efficient one.

(extracted from <High performance mysql>)