How to check contents of an index in a database?-CodePudding

Is there any way that I can see what is stored in those indexes?

I understand the query mentioned below lists down all the schema indexes and their details: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; (taken from Mark Byers's response to How to see indexes for a database or table in MySQL?)

CodePudding user response：

I'll answer for MySQL only, because PostgreSQL's information_schema does not have a table called statistics.

SELECT <column> FROM <table> shows the values that are indexed. In other words, the leaf nodes of the index are exactly the values in the columns the index was created for.

If you want to see the index data structure itself, MySQL provides no access to this. It depends on the table's storage engine.

There are some tools developed by an InnoDB expert Jeremy Cole that can inspect the internal structure of index pages. See https://blog.jcole.us/innodb/ for his blogs on this subject and https://github.com/jeremycole/innodb_ruby for his tools.

CodePudding user response：

You can use the query you mentioned to retrieve a list of indexes and their details for a specific schema. You can also use the following query to get more detailed information about a specific index:

SHOW INDEX FROM your_table_name WHERE Key_name = 'your_index_name';

This query should return the following information about the specified index:

Non_unique: Whether the index is unique or not Key_name: The name of the index Seq_in_index: The sequence number of the column within the index Column_name: The name of the column that is indexed Collation: The collation of the indexed column Cardinality: The number of unique values in the indexed column Sub_part: The length of indexed prefix for the column Packed: Information about the packed state of the index Null: Whether the indexed column can contain NULL values Index_type: The type of the index Comment: Additional information about the index

You can also use the following query to get the indexes for all tables in a specific schema:

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'your_schema'
ORDER BY TABLE_NAME, INDEX_NAME;

This should give you the table name, index name and column name that the index is on.

You can also use the following query to get the indexes for all tables in a specific schema and see the column in the index

SELECT t.table_name,i.index_name, group_concat(i.column_name) as columns
FROM information_schema.statistics i
JOIN information_schema.tables t on t.table_name = i.table_name
WHERE i.table_schema = 'your_schema'
GROUP BY i.table_name,i.index_name
ORDER BY t.table_name,i.index_name;

This should give you the table name, index name and columns that the index is on.

In any case, you should replace "your_schema" with the name of the schema you want to get the information for and "your_table_name" with the name of the table you want to get information for.