Boost SQL Performance Without Rewriting Queries: Indexes, Partitioning, Caching

In database operations, slow SQL queries are a common pain point, but performance can be dramatically improved without rewriting the SQL itself by using external techniques such as indexes, parameters, and architectural changes.

1. Index Optimization – Immediate Gains

When no index exists, queries often perform full table scans; adding appropriate indexes yields instant performance boosts.

-- Original slow SQL
SELECT * FROM orders
WHERE customer_id = 123
  AND order_date > '2023-01-01';

-- Add composite index
CREATE INDEX idx_customer_order_date
ON orders(customer_id, order_date);

After the index is created, the database can locate rows directly, eliminating full scans.

2. Database Parameter Tuning

Adjusting database configuration parameters helps the optimizer choose better execution plans.

# MySQL (my.cnf)
innodb_buffer_pool_size = 4G;
sort_buffer_size = 8M;

# PostgreSQL
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET default_statistics_target = 200;

3. Table Structure Optimization

Partitioning and column reduction lower overhead.

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    customer_id BIGINT,
    order_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

When querying 2023 data, only partition p2023 is scanned.

4. Introduce Caching Layer

Cache hot query results with Redis to avoid repeated database hits.

// Java example
String cacheKey = "orders:customer:123:2023";
String result = redis.get(cacheKey);
if (result == null) {
    result = jdbc.query("SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01'");
    redis.set(cacheKey, result, 3600); // cache for 1 hour
}
return result;

5. Read‑Write Splitting & Load Balancing

Configure master‑slave replication and route read‑only SELECT statements to replicas.

-- On replica
CHANGE MASTER TO
  MASTER_HOST='master-db',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=120;
START SLAVE;

6. Sharding (Database‑Level Partitioning)

Example using ShardingSphere YAML configuration to distribute data across multiple nodes.

rules:
- !SHARDING
  tables:
    orders:
      actualDataNodes: ds$->{0..3}.orders_$->{0..7}
      tableStrategy:
        standard:
          shardingColumn: customer_id
          shardingAlgorithmName: order_hash
  shardingAlgorithms:
    order_hash:
      type: HASH_MOD
      props:
        sharding-count: 8

7. Index Maintenance & Health Checks

Too many or fragmented indexes can degrade performance; regular inspection and rebuilding are essential.

# MySQL
SHOW INDEX FROM orders;
ALTER TABLE orders DROP INDEX idx_old, ADD INDEX idx_new(customer_id, order_date);

# PostgreSQL
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
REINDEX INDEX idx_customer_order_date;

8. Statistics Refresh

Keeping optimizer statistics up‑to‑date enables accurate cost estimation.

# MySQL
ANALYZE TABLE orders;
# PostgreSQL
ANALYZE orders;

9. Hot‑Cold Data Separation

Archive historical rows to a separate table, keeping the primary table small for faster queries.

INSERT INTO orders_history
SELECT * FROM orders WHERE order_date < '2023-01-01';
DELETE FROM orders WHERE order_date < '2023-01-01';

10. Compression & Storage Optimization

Compress InnoDB rows to reduce I/O.

CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    message TEXT
) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

11. Hardware & OS Tuning

Replace HDD with SSD for tens‑fold I/O improvement.

Use RAID/NVMe for higher throughput.

Linux sysctl: vm.swappiness=1, disable transparent hugepages.

12. Middleware & Proxy Layer

Route all SELECT statements to replicas using ProxySQL.

INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (1, '^SELECT.*', 20); -- send SELECTs to slave group

13. Storage Engine Choice

Select the engine that matches the workload characteristics.

# MySQL
CREATE TABLE t_innodb (id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t_myisam (id INT PRIMARY KEY) ENGINE=MyISAM;

# PostgreSQL
CREATE UNLOGGED TABLE session_temp (
    session_id UUID PRIMARY KEY,
    data JSONB
);

14. Slow‑Query Investigation Workflow

Enable slow‑query logging and analyze execution plans.

# MySQL
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

# PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;

Conclusion

The core optimization philosophy is to start with index improvements, then progressively adopt caching, read‑write splitting, sharding, and hardware upgrades, ultimately building a high‑performance, scalable database architecture.