Bookmark and Share
The query optimizer and Last_query_cost
(Publish Date: 2009-11-18 12:15pm, Total Visits: 891, Today: 1, This Week: 4, This Month: 6)

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.

(extracted from <High performance of MySQL)