Tagged articles

Query Planner

10 articles · Page 1 of 1

May 27, 2024 · Databases

SQLite 3.46.0 Release Highlights and New Features

SQLite 3.46.0 introduces extensive PRAGMA enhancements, new date‑time modifiers, support for underscore digits in numeric literals, a json_pretty() function, significant query planner optimizations, improved JSON handling, and the ability to use large hexadecimal literals as column defaults, all while maintaining its public‑domain, self‑contained nature.

Database EnginePRAGMAQuery Planner

0 likes · 4 min read

SQLite 3.46.0 Release Highlights and New Features

Aikesheng Open Source Community

Sep 19, 2023 · Databases

MySQL Deadlock Analysis and Index Optimization Using ICP

This article examines a MySQL deadlock case involving DELETE and UPDATE statements, explains why the primary key index is locked despite non‑key predicates, analyzes execution plans and optimizer trace, and proposes index restructuring and query hints to resolve the issue.

DeadlockICPMySQL

0 likes · 23 min read

MySQL Deadlock Analysis and Index Optimization Using ICP

Java Architect Essentials

Apr 18, 2023 · Databases

How MySQL Calculates Execution Cost for Index Selection and Why It May Choose Suboptimal Plans

This article explains how MySQL estimates the I/O and CPU costs of different indexes when executing COUNT(*) and other queries, demonstrates the calculations with a concrete example on a 100k‑row table, and shows why the optimizer sometimes picks a full‑table scan over a seemingly better index.

Database PerformanceEXPLAINMySQL

0 likes · 10 min read

How MySQL Calculates Execution Cost for Index Selection and Why It May Choose Suboptimal Plans

Hacker Afternoon Tea

Mar 30, 2022 · Databases

Understanding Citus Distributed PostgreSQL: Query Processing Architecture

The article explains how a Citus cluster—comprising a coordinator and multiple workers—splits and routes SQL queries, detailing the distributed planner, executor, push‑pull handling of subqueries/CTEs, and a step‑by‑step EXPLAIN walkthrough of a top‑20 page‑views query.

CitusDistributed QueryEXPLAIN

0 likes · 10 min read

Understanding Citus Distributed PostgreSQL: Query Processing Architecture

Laravel Tech Community

Feb 24, 2022 · Databases

SQLite 3.38.0 Released with New JSON Operators, Enhanced Date/Time Functions, and Query Planner Improvements

SQLite version 3.38.0 introduces new JSON operators compatible with MySQL and PostgreSQL, built‑in JSON functions, added unixepoch() and auto/julianday modifiers for date/time, renamed printf() to format(), enhanced virtual table interfaces, CLI columnar output options, and query planner optimizations using Bloom filters and balanced merge trees.

CLIDatabase EngineQuery Planner

0 likes · 4 min read

SQLite 3.38.0 Released with New JSON Operators, Enhanced Date/Time Functions, and Query Planner Improvements

Laravel Tech Community

Nov 1, 2021 · Databases

Vitess 12 Release: New Gen4 Planner, VTAdmin Enhancements, RBAC, and Benchmarking Improvements

Vitess 12, the latest major release of the MySQL clustering solution, introduces the experimental Gen4 query planner, enhanced VTAdmin multi‑cluster management, role‑based access control, updated benchmarking tools, and more inclusive naming conventions to improve scalability and cloud deployment.

Database ScalingMySQLQuery Planner

0 likes · 5 min read

Vitess 12 Release: New Gen4 Planner, VTAdmin Enhancements, RBAC, and Benchmarking Improvements

360 Zhihui Cloud Developer

Jul 4, 2019 · Databases

Mastering SQL Joins: How Optimizers Choose the Best Access Path

This article explains how SQL optimizers choose join methods and table order, compares nested loop and merge joins, outlines factors influencing table selection, and offers practical guidance for improving multi‑table query performance.

Database PerformanceMerge JoinNested Loop

0 likes · 8 min read

Mastering SQL Joins: How Optimizers Choose the Best Access Path

ITPUB

Jul 11, 2016 · Databases

Inside PostgreSQL: How the Database Kernel Works and Can Be Extended

This article explains PostgreSQL's kernel architecture, from client connection handling through parsing, rewriting, planning, and execution, illustrates scan and join algorithms, shows how to extend the kernel and build clustered deployments, and introduces Foreign Data Wrappers for integrating external data sources.

ClusterForeign Data WrapperIndex Scan

0 likes · 13 min read

Inside PostgreSQL: How the Database Kernel Works and Can Be Extended

ITPUB

May 31, 2016 · Databases

Inside PostgreSQL: How SQL Queries Are Processed from Start to Finish

This article walks through PostgreSQL's complete SQL execution pipeline, detailing each component—from the Main entry point and Postmaster process to parsing, traffic coordination, query rewriting, plan generation, and execution—while comparing its process architecture to other database systems.

Database InternalsPostgreSQLPostmaster

0 likes · 6 min read

Inside PostgreSQL: How SQL Queries Are Processed from Start to Finish

Java High-Performance Architecture

Oct 4, 2015 · Databases

Why Does MySQL Ignore an Index Until the Date Range Is Narrowed?

Although a users table has an index on the create_time column, a query spanning a broad date range triggers a full table scan, but narrowing the range enables the optimizer to use the index, illustrating how MySQL’s cost‑based optimizer decides between index lookup and table scan.

EXPLAINMySQLQuery Planner

0 likes · 2 min read

Why Does MySQL Ignore an Index Until the Date Range Is Narrowed?