Cannot find the constants for the Optimizer Cost Model in v11.5 - Source

Revision: 122832
User: Mike Zhao
Date: 2023-01-14 17:10

We are using MariaDB v11.5, and according to the [[ https://mariadb.com/kb/en/the-optimizer-cost-model-from-mariadb-11-0/#new-cost-model | New Cost Model ]] in MariaDB official Knowledge Base, there should be a table `information_schema.optimizer_costs` containing the constants used for running time estimation. And the online document provides the below sample query:

{{{
 select * from information_schema.optimizer_costs where engine="default"\G
*************************** 1. row ***************************
                         ENGINE: default
       OPTIMIZER_DISK_READ_COST: 10.240000
OPTIMIZER_INDEX_BLOCK_COPY_COST: 0.035600
     OPTIMIZER_KEY_COMPARE_COST: 0.011361
...
}}}

In our testing server, the Optimizer Trace is working on this database instance, and during test, we have retrieved the cost estimation as shown in the screenshot below. However, we are unable to find the table `information_schema.optimizer_costs`.

**Question:**

So we wonder where to look for the table.

**Screenshot of Optimizer Trace:**

{{{
MariaDB [database]> SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+---------------------
| TRACE               
+---------------------
| {
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select count(0) AS `count(*)` from eav_value"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "eav_value",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "eav_value",
                "table_scan": {
                  "rows": 2207198,
                  "cost": 9003
                }
              }
            ]
          },
...

}}}

Answer

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Cannot find the constants for the Optimizer Cost Model in v11.5 - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB