Database Optimization Techniques: Performance Tuning Guide

-- Index frequently queried columns
CREATE INDEX idx_user_email ON users(email);

-- Index foreign keys
CREATE INDEX idx_post_user_id ON posts(user_id);

-- Composite indexes for multi-column queries
CREATE INDEX idx_user_status_role ON users(status, role);

-- Bad
SELECT * FROM users WHERE id = 123;

-- Good
SELECT id, name, email FROM users WHERE id = 123;

-- Always limit large result sets
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

// Bad: N+1 queries
users.forEach(user => {
  const posts = db.query('SELECT * FROM posts WHERE user_id = ?', [user.id]);
});

// Good: Single query with JOIN
const usersWithPosts = db.query(`
  SELECT u.*, p.* 
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
`);

// Configure connection pool
const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  queueLimit: 0
});

// Cache frequently accessed data
const cache = new Map();

async function getUser(id) {
  if (cache.has(id)) {
    return cache.get(id);
  }
  
  const user = await db.query('SELECT * FROM users WHERE id = ?', [id]);
  cache.set(id, user);
  return user;
}

-- Use query cache (MySQL)
SET GLOBAL query_cache_size = 67108864;
SET GLOBAL query_cache_type = 1;

-- Avoid over-normalization
-- Balance between normalization and performance

-- Use smallest appropriate type
TINYINT instead of INT for small numbers
VARCHAR(255) instead of TEXT when possible
DATE instead of DATETIME when time not needed

-- Partition large tables by date
CREATE TABLE logs (
  id INT,
  created_at DATE,
  data TEXT
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

// Bad: Multiple individual inserts
users.forEach(user => {
  db.query('INSERT INTO users (name, email) VALUES (?, ?)', [user.name, user.email]);
});

// Good: Batch insert
const values = users.map(u => [u.name, u.email]);
db.query('INSERT INTO users (name, email) VALUES ?', [values]);

VACUUM ANALYZE;

OPTIMIZE TABLE users;