Indexes improve database performance by creating fast access paths for table data. They help retrieve information quickly, especially when working with large datasets. The SHOW INDEXES statement allows users to view and manage the existing indexes of a table.
- Improves query execution by using optimized search paths.
- Helps analyze existing indexes for better optimization.
- Affects write operations because indexes need maintenance.
Syntax:
SHOW INDEXES FROM table_name;- This command displays all the indexes created for a particular table.
- It providing details such as index name, column names and more.
Example Of SQL Show Indexes
Create a table, add indexes and view index details to understand how different indexes improve query performance.
Step 1: Create the orders Table
CREATE TABLE orders (
order_id INT AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10, 2),
PRIMARY KEY(order_id),
INDEX idx_customer_id (customer_id), -- Regular index on customer_id
UNIQUE INDEX unique_order_date (order_date), -- Unique index on order_date
INDEX invisible_index (total_amount) INVISIBLE, -- Invisible index on total_amount
INDEX composite_index (customer_id, order_date) COMMENT 'By customer and order date' -- Composite index on multiple columns
);
Step 2: Show Indexes on the orders Table
SHOW INDEXES FROM orders;Output:
- The output shows all the indexes associated with the orders table, along with details like index name, column names, uniqueness, cardinality and index type.
- It helps you understand how each index is structured and whether it's used for specific queries.
