tencent cloud

Feedback

Invisible Index

Last updated: 2024-04-25 11:14:26

    Overview

    You may require the capability to make an index invisible to determine whether it can be deleted By making an index as invisible, you can test the impact of its deletion on query performance before deleting it. If the index is being used by any program or database user, an error will occur or be reported. This feature is now available to MySQL 5.7 and later versions, not just limited to MySQL 8.0.

    Supported Versions

    TDSQL-C for MySQL 5.7 (kernel version 2.0.23/2.1.9) or later.
    ‌TDSQL-C for MySQL 8.0 (kernel version 3.1.10) or later.

    Use Cases

    Before deleting an index, you can make it invisible to see if it is still in use. If not, it can be securely deleted.

    Use Limits

    Run the following statements to create an invisible index or make an index invisible:
    CREATE TABLE t1 (
    i INT,
    j INT,
    k INT,
    INDEX i_idx (i) INVISIBLE
    ) ENGINE = InnoDB;
    CREATE INDEX j_idx ON t1 (j) INVISIBLE;
    ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
    Run the following statements to make an index visible:
    ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
    ALTER TABLE t1 ALTER INDEX i_idx VISIBLE
    
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support