Database Optimization Archives - ITFROMZERO

Mastering Common Table Expressions (CTEs) and Recursive CTEs in MySQL 8: Subquery Optimization and Hierarchical Data Handling

By IT From Zero November 6, 2026

Master the technical inner workings of standard and recursive CTEs in MySQL 8. Learn how to traverse hierarchies, generate data sequences, and analyze execution plans to avoid production I/O bottlenecks.

MySQL

How to Diagnose and Resolve MySQL I/O Bottlenecks Using Performance Schema and sys Schema

By IT From Zero November 2, 2026

A complete guide to diagnosing and fixing MySQL I/O bottlenecks using Performance Schema and sys Schema, pinpointing full table scans and disk-spilling queries.

Database

Optimizing Compound Indexes in MongoDB with the ESR Rule: Prevent ‘Sort Exceeded Memory Limit’ Errors

By IT From Zero November 2, 2026

Apply the ESR (Equality, Sort, Range) rule to design effective MongoDB compound indexes, eliminating in-memory sorting and cutting query times from 1.8s to 4ms.

MySQL

A Guide to Configuring Table Partitioning in MySQL: Boosting Large Table Performance with RANGE and LIST

By IT From Zero October 25, 2026

Are tables over 50 million rows causing sluggish queries and crushing your database during DELETE operations? Table Partitioning (RANGE & LIST) in MySQL isolates data across partitions, accelerates query speeds, and reclaims disk space in seconds.

MySQL

Enable MySQL Slow Query Log and Use pt-query-digest to Find Database Bottlenecks

By IT From Zero October 24, 2026

A guide to enabling MySQL Slow Query Log without restarting the server and using pt-query-digest to dissect and rank CPU-bottlenecking queries in production.

Development

A Practical Guide to pgvector on PostgreSQL: Vector Embeddings and Semantic Search for Node.js

By IT From Zero October 19, 2026

Practical production lessons deploying pgvector on PostgreSQL: Node.js integration, HNSW index tuning, and memory optimization techniques to slash latency from 8s to 20ms.

MySQL

Optimizing MySQL Queries with Covering Indexes: The Secret to Eliminating Table Lookups

By IT From Zero October 18, 2026

Table Lookups are the primary cause of I/O bottlenecks on large datasets, even when queries use an index. Master Covering Indexes and Deferred Joins to slash query latency from seconds down to milliseconds.

MySQL

Configuring ProxySQL for MySQL: Read/Write Splitting, Connection Pooling, and Query Caching

By IT From Zero October 16, 2026

ProxySQL serves as an intelligent bridge between your application and MySQL database clusters. Here is a practical guide to configuring transparent read/write splitting, optimizing connection pooling, and enabling in-memory query caching without modifying application code.

Python

Optimizing PostgreSQL Performance in Python with asyncpg: Resolving I/O Bottlenecks and Ingesting 100k Records/s

By IT From Zero October 9, 2026

Real-world lessons from a production I/O bottleneck: A complete guide to migrating to asyncpg, configuring connection pools properly, and bulk-ingesting 100k records in 0.8s via PostgreSQL binary protocol.

MySQL

Optimizing MySQL InnoDB Table Fragmentation: Using OPTIMIZE TABLE to Reclaim Disk Space

By IT From Zero October 7, 2026

Why does deleting millions of records fail to free up MySQL disk space? Here is how to detect InnoDB table fragmentation and safely use OPTIMIZE TABLE to reclaim disk space on production.