sql Archives - ITFROMZERO - Share tobe shared!

Diagnosing and Resolving MySQL Deadlocks: InnoDB Log Analysis and Lock Ordering Techniques

By IT From Zero November 7, 2026

A hands-on guide to resolving production MySQL deadlocks at 2 AM. Learn to analyze SHOW ENGINE INNODB STATUS logs, configure innodb_print_all_deadlocks, and design transactions to eliminate circular waits.

MySQL

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

MySQL Event Scheduler Guide: Automating Scheduled Tasks and Periodic Database Maintenance

By IT From Zero November 2, 2026

Automate maintenance tasks, junk data cleanup, and periodic metrics aggregation directly inside MySQL using the Event Scheduler. A complete guide from enabling the engine to writing self-executing SQL and monitoring background events.

Database

Designing and Optimizing Hierarchical Tree Data in SQL: A Practical Comparison of 4 Models

By IT From Zero October 28, 2026

A practical comparison of four hierarchical data models in SQL—Adjacency List, Path Enumeration, Nested Sets, and Closure Table—with real-world benchmarks and query optimization tips.

Database

Diagnosing and Resolving Deadlocks in PostgreSQL: Inspecting Lock Trees and Configuring Proper Timeouts

By IT From Zero October 26, 2026

An in-depth look at the root causes of PostgreSQL deadlocks, step-by-step reproduction, Lock Tree query techniques to locate root blockers, and timeout tuning to safeguard your system.

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.

Development

Kysely: A Query Builder Alternative to Prisma and TypeORM for TypeScript Projects

By IT From Zero September 21, 2026

Kysely is the perfect alternative to Prisma and TypeORM. Learn how to build a high-performance Type-safe SQL Query Builder, reduce server RAM, and optimize your Node.js development workflow.

Database

Designing Polymorphic Relationships in SQL: 3 Architectures from Production Experience

By IT From Zero September 21, 2026

Learn how to design efficient Polymorphic relationships in SQL. Compare 3 common methods and discover index optimization tips for high-scale systems.

Database

Dolt: When Databases Marry Git – An A-Z Guide to Data Version Control

By IT From Zero September 13, 2026

Dolt is the first SQL database that lets you commit, branch, and merge data just like Git. Learn how Dolt solves data versioning and safety challenges in this article.

Database

Mastering dbt Core with PostgreSQL: From Fragmented SQL to Standardized Data Workflows

By IT From Zero September 9, 2026

Eliminate the headache of manual SQL management. A detailed guide on using dbt Core with PostgreSQL to automate data transformations, data testing, and documentation.