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

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

-- 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'

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

-- 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 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 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;

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

-- Drop index
DROP INDEX idx_name ON table;

-- Atau dengan ALTER TABLE
ALTER TABLE table DROP INDEX idx_name;

-- 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';