Information Schema INNODB_SYS_INDEXES Table

The Information Schema INNODB_SYS_INDEXES table contains information about InnoDB indexes.

The PROCESS privilege is required to view the table.

It has the following columns:

Column	Description
`INDEX_ID`	A unique index identifier.
`NAME`	Index name, lowercase for all user-created indexes, or uppercase for implicitly-created indexes; `PRIMARY` (primary key), `GEN_CLUST_INDEX` (index representing primary key where there isn't one), `ID_IND`, `FOR_IND` (validating foreign key constraint) , `REF_IND`.
`TABLE_ID`	Table identifier, matching the value from INNODB_SYS_TABLES.TABLE_ID.
`TYPE`	Numeric type identifier; one of `0` (secondary index), `1` (clustered index), `2` (unique index), `3` (primary index), `32` (full-text index).
`N_FIELDS`	Number of columns in the index. GEN_CLUST_INDEX's have a value of 0 as the index is not based on an actual column in the table.
`PAGE_NO`	Index B-tree's root page number. `-1` (unused) for full-text indexes, as they are laid out over several auxiliary tables.
`SPACE`	Tablespace identifier where the index resides. `0` represents the InnoDB system tablespace, while any other value represents a table created in file-per-table mode (see the innodb_file_per_table system variable). Remains unchanged after a TRUNCATE TABLE statement, and not necessarily unique.

Example

SELECT * FROM information_schema.INNODB_SYS_INDEXES LIMIT 3\G
*************************** 1. row ***************************
       INDEX_ID: 11
           NAME: ID_IND
       TABLE_ID: 11
           TYPE: 3
       N_FIELDS: 1
        PAGE_NO: 302
          SPACE: 0
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 12
           NAME: FOR_IND
       TABLE_ID: 11
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 303
          SPACE: 0
MERGE_THRESHOLD: 50
*************************** 3. row ***************************
       INDEX_ID: 13
           NAME: REF_IND
       TABLE_ID: 11
           TYPE: 3
       N_FIELDS: 1
        PAGE_NO: 304
          SPACE: 0
MERGE_THRESHOLD: 50
3 rows in set (0.00 sec)

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.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/information-schema-innodb_sys_indexes-table/