Question 21114 in Mysql Question posted in
The official MySQL documentation can be found here.

How to check contents of an index in a database? – Mysql

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?)

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search