List of used index coluns (mysql/mariadb)

user_1950907 0
Viewed 64

EXPLAIN shows only key length.

For example:

We have an complex index on a table.

IDX_KEY (col1, col2, col3) (let's all the columns are integer type)

if the key length (from "explain" output) = 4 then only the col1 of IDX_KEY index is used in the query.

if 8 then col1 and col2.

if 12 then all the columns in the index are used.

My question: is there a way to determine columns list without calculations?

UPD:

The real table has a lot of fields and indexes. But for example, let it has such definition: CREATE TABLE `tbl` (`col1` unsigned int not null,`col2` unsigned int not null,`col3` unsigned int not null, primary key (`col1`,`col2`,`col2`));

Then the explain can be like this (in case of using only one column from the index):

"id"  "select_type"  "table"  "type"   "possible_keys"  "key"      "key_len"  "ref"  "rows"  "Extra"
"1"   "PRIMARY"      "tbl"    "range"  "PRIMARY"        "PRIMARY"  "4"        \N     "1"     ""
mariadb mysql
1 Answers

Relying on the key length makes no sense, as it depends on the storage engine and MariaDB version used. In the current MariaDB version (10.5) and InnoDB, the key length is not 4 but 5.

To get more information from the optimizer you should use ANALYZE command. Using it together with FORMAT=JSON (this feature is available since MariaDB 10.1) it will give you some more detailed information, including the used keys.

MariaDB [test]> analyze format=JSON select col1,col2,col3 from t2 where col1=1 and col2=2 \G
*************************** 1. row ***************************
ANALYZE: {
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.082303568,
    "table": {
      "table_name": "t2",
      "access_type": "ref",
      "possible_keys": ["col1"],
      "key": "col1",
      "key_length": "10",
      "used_key_parts": ["col1", "col2"],
      "ref": ["const", "const"],
      "r_loops": 1,
      "rows": 1,
      "r_rows": 1,
      "r_table_time_ms": 0.027733218,
      "r_other_time_ms": 0.041487106,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    }
  }
}
1 row in set (0.001 sec)

Addendum: I didn't see the comment from Rick James before, the EXPLAIN FORMAT=JSON works too, and if you only need the used keys you should use this command.

user_69305010
Related