/*
===========================================================================
===
MySQL Indexing
-------------------------------------------------------------------------------
Demonstrates various index types in MySQL including:
- Primary Key (Clustered Index in InnoDB)
- Secondary (Non-Clustered) Indexes
- Composite Indexes
- Unique Indexes
- Filtered Index (via Generated Column workaround)
Also shows:
- Monitoring indexes
- Updating statistics
- Defragmentation
===========================================================================
======
*/
-- Step 1: Create sample database and tables
CREATE DATABASE IF NOT EXISTS Sales;
USE Sales;
-- Drop if exists for clean re-run
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Products;
-- Create Customers table
CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Score INT
);
-- Insert sample data
INSERT INTO Customers (FirstName, LastName, Country, Score) VALUES
('John', 'Brown', 'USA', 600),
('Alice', 'Smith', 'USA', 450),
('Bob', 'Taylor', 'Germany', 700),
('Maria', 'Gonzalez', 'Spain', 550),
('Rahul', 'Sharma', 'India', 800);
-- Create Products table
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
Product VARCHAR(100),
Category VARCHAR(50)
);
-- Insert sample data
INSERT INTO Products (Product, Category) VALUES
('Laptop', 'Electronics'),
('Phone', 'Electronics'),
('Shoes', 'Apparel'),
('Watch', 'Accessories'),
('Tablet', 'Electronics');
/*
===========================================================================
===
Clustered and Non-Clustered Indexes in MySQL
===========================================================================
=== */
-- Syntax : CREATE (Clustured | Non Clustered ) INDEX Name ON Table Name (col1, col2,
col3....); (default non clustured)
-- SHOW INDEXES FROM table_name;
-- SHOW KEYS FROM table_name;
Show Indexes from [Link];
SHOW KEYS FROM [Link];
-- Create a copy of Customers (heap-like table, no PK initially)
Use SalesDB;
DROP TABLE IF EXISTS DemoCustomers;
CREATE TABLE DemoCustomers AS
SELECT * FROM Customers;
-- Add a PRIMARY KEY (Clustered Index in MySQL)
ALTER TABLE DemoCustomers ADD PRIMARY KEY (CustomerID);
-- Create Secondary Indexes
CREATE INDEX idx_LastName ON DemoCustomers (LastName);
CREATE INDEX idx_FirstName ON DemoCustomers (FirstName);
DROP INDEX idx_LastName ON DemoCustomers;
Show Indexes from [Link];
Explain Select * from [Link];
-- Create Composite Index (Country, Score)
CREATE INDEX idx_CountryScore ON DemoCustomers (Country, Score);
-- Query using composite index
SELECT *
FROM DemoCustomers
WHERE Country = 'USA' AND Score > 500;
-- Query that likely won't use composite index due to order
SELECT *
FROM DemoCustomers
WHERE Score > 500 AND Country = 'USA';
/*
===========================================================================
===
Leftmost Prefix Rule Explanation
===========================================================================
=== */
-- For index (Country, Score), queries that use:
-- Country
-- Country + Score
-- will use the index efficiently.
-- Queries using Score only will not benefit fully.