Jenis-Jenis Index di MySQL: Primary, Unique, Composite, Full-Text, dan Spatial

Memahami berbagai jenis index adalah kunci optimasi database. Setelah menganalisis query patterns untuk berbagai aplikasi, saya akan berbagi penggunaan optimal dari setiap jenis index.

Primary Key Index

Karakteristik

• Unik dan NOT NULL
• Satu per table
• Clustered index untuk InnoDB

-- Auto-increment integer (most common)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);
-- Natural key (gunakan dengan hati-hati)
CREATE TABLE countries (
iso_code CHAR(2) PRIMARY KEY,
name VARCHAR(100)
);
-- Composite primary key
CREATE TABLE order_items (
order_id INT,
item_number INT,
PRIMARY KEY (order_id, item_number)
);

Unique Index

Karakteristik

• Unik values, bisa NULL
• Multiple per table
• Automatic index creation

-- Single column unique
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);
-- Named unique constraint
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(50),
CONSTRAINT uk_sku UNIQUE (sku)
);
-- Multiple NULL allowed
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) UNIQUE,  -- Bisa multiple NULL
ssn VARCHAR(20) UNIQUE
);

Composite Index (Multi-Column)

Karakteristik

• Multiple columns dalam satu index
• Order matters (leftmost prefix)
• Berguna untuk multi-column WHERE clauses

-- Composite index
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    created_at TIMESTAMP,
    INDEX idx_user_status_date (user_id, status, created_at)
);
-- Queries yang ter-cover:
-- WHERE user_id = ?
-- WHERE user_id = ? AND status = ?
-- WHERE user_id = ? AND status = ? AND created_at > ?
-- WHERE user_id = ? AND created_at > ? (partial)
-- Queries yang TIDAK ter-cover:
-- WHERE status = ? (bukan leftmost column)
-- WHERE created_at > ? (bukan prefix)

Best Practices Composite Index

-- Column dengan cardinality tinggi di depan
CREATE INDEX idx_high_card ON table(high_cardinality_col, low_cardinality_col);
-- Equality columns sebelum range columns
CREATE INDEX idx_eq_range ON table(equality_col, range_col);
-- Contoh optimal
CREATE TABLE events (
user_id INT,
event_type VARCHAR(20),
event_date DATE,
INDEX idx_user_type_date (user_id, event_type, event_date)
);
-- WHERE user_id = 123 AND event_type = 'login' AND event_date > '2024-01-01'

Full-Text Index

Karakteristik

• Untuk text search
• Hanya untuk CHAR, VARCHAR, TEXT
• MyISAM dan InnoDB (5.6+)

-- Single column full-text
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content)
);
-- Multi-column full-text
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_title_content (title, content)
);
-- Penggunaan
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('tutorial mysql' IN NATURAL LANGUAGE MODE);
-- Boolean mode
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

Spatial Index

Karakteristik

• Untuk geometry data types
• MyISAM dan InnoDB (5.7+)
• Optimized untuk spatial queries

-- Create table dengan geometry column
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL,
    SPATIAL INDEX idx_location (location)
);
-- Insert data
INSERT INTO locations (name, location) VALUES
('Office', POINT(106.8456, -6.2088));
-- Spatial query
SELECT * FROM locations 
WHERE ST_Distance(location, POINT(106.8456, -6.2088)) < 1000;

Covering Index

Karakteristik

• Index yang contains all columns needed untuk query
• No table access required (Index Only Scan)
• Significant performance improvement

-- Covering index example
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    total_amount DECIMAL(10,2),
    INDEX idx_user_status_total (user_id, status, total_amount)
);
-- Query yang ter-cover sepenuhnya:
SELECT user_id, status, total_amount 
FROM orders 
WHERE user_id = 123 AND status = 'completed';
-- MySQL bisa answer dari index saja!
-- Query yang tidak ter-cover (needs id):
SELECT id, user_id, status, total_amount FROM orders WHERE user_id = 123;
-- Perlu include id dalam index atau access table

Prefix Index

Karakteristik

• Index sebagian dari column (prefix)
• Berguna untuk long VARCHAR/TEXT columns
• Trade-off: smaller index, less selective

-- Prefix index untuk email (20 karakter pertama)
CREATE INDEX idx_email_prefix ON users(email(20));
-- Berguna untuk URL
CREATE INDEX idx_url_prefix ON links(url(50));
-- Cek selectivity
SELECT 
COUNT(DISTINCT email) AS total_unique,
COUNT(DISTINCT LEFT(email, 20)) AS prefix_unique,
COUNT(DISTINCT LEFT(email, 20)) / COUNT(DISTINCT email) AS selectivity
FROM users;

Index Management

1. Create Index

-- Create index
CREATE INDEX idx_name ON table(column);
-- Create unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);
-- Create composite index
CREATE INDEX idx_composite ON table(col1, col2, col3);
-- Create full-text index
CREATE FULLTEXT INDEX idx_ft ON articles(content);

2. Drop Index

-- Drop index
DROP INDEX idx_name ON table;
-- Atau dengan ALTER TABLE
ALTER TABLE table DROP INDEX idx_name;

3. Show Indexes

-- Show indexes untuk table
SHOW INDEX FROM table;
-- Atau
SHOW CREATE TABLE table;
-- Query information_schema
SELECT 
INDEX_NAME,
COLUMN_NAME,
CARDINALITY,
SUB_PART
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'users';

Kesimpulan

Pilih index berdasarkan query patterns:
– Primary Key: Setiap table
– Unique: Prevent duplicates
– Composite: Multi-column WHERE
– Full-Text: Text search
– Spatial: Location data
– Covering: Index-only queries

Index yang tepat adalah kunci performa optimal!