This article explains essential database design principles—including normalization, denormalization, join avoidance, and various sharding techniques—while also covering scaling strategies such as vertical upgrades, horizontal partitioning, and the use of flash storage to boost performance.
Time‑series data, defined by a timestamp field, appears everywhere, and the article explains how to choose an appropriate time‑series database by comparing two schema models, their query patterns, performance trade‑offs, and why modern solutions like Elasticsearch, columnar stores, and Druid excel at real‑time massive aggregation.
The ITPUB community revived its SQL Database Programming Contest, outlining judges, prize tiers, and detailed rules, then presenting two Oracle‑SQL puzzles—one to generate every possible tic‑tac‑toe endgame board and another to determine a forced winning strategy for a given board—plus an optional extension for larger m,n,k games.
This article explains how implicit lock requests in PostgreSQL can lead to surprising lock waits, demonstrates debugging steps with lock_debug and a reporting script, and offers practical tips like lock_timeout and statement_timeout to prevent prolonged blocking scenarios.
This comprehensive guide explains the essence of Oracle SQL tuning, presents a step‑by‑step optimization roadmap, walks through real‑world case studies—including a high‑CPU query fixed with hints and query rewrites—and details how to capture and interpret accurate execution plans while addressing optimizer parameters and common pitfalls.
This guide explains two flash‑sale scenarios—single‑item seckill with a unique index and multi‑item seckill with quantity tracking—detailing table structures, SQL statements, concurrency handling, advantages, drawbacks, and an optimized version without locks, illustrated with step‑by‑step diagrams.
The announcement details three internal technical training sessions covering XML serialization fundamentals, .NET parallel programming basics, and essential SQL query concepts and optimization, including instructors, topics, locations, target audiences, and scheduled times for each course.
This article explains how MySQL versions up to 5.5 process and optimize views, outlines the exact conditions required for view merge rewriting, compares derived table handling with the SQL standard, and provides concrete test cases to illustrate performance pitfalls.
A father turned his daughter's third‑grade math challenge—finding a five‑digit number that becomes its reverse when multiplied by four—into a collaborative coding exercise, showcasing three distinct Oracle SQL solutions that generate and test digit permutations to reveal the unique answer.
This article explains why concurrent read‑write operations on a shared balance can cause inconsistencies, illustrates the problem with a purchase‑balance example, and shows how a Compare‑And‑Set (CAS) update clause guarantees atomicity and preserves data integrity.
This article explains why using SELECT * is problematic and demonstrates a simple technique—adding a computed column that triggers a divide‑by‑zero error—to block SELECT * queries while still allowing column‑specific selections.
This article examines three SQL pagination strategies for a 100‑million‑row favorites table, compares their correctness and performance using EXPLAIN analysis, and demonstrates why a GROUP BY approach with proper indexing yields the most reliable and fast results.
This article explains how MySQL's query cache works, why it uses the full SQL statement as a key, and lists ten typical situations—such as using SQL_NO_CACHE, volatile functions, or locks—that prevent a query from being cached.
This article explains how to translate a classic SQL many‑to‑many schema for articles and tags into a MongoDB design, showing document structures and practical queries for listing tags, retrieving articles, and filtering by specific tags.
This guide explains how to use the SQL EXPLAIN statement to analyze query execution plans, detailing each output column such as id, select_type, table, possible_key, key, key_len, type, rows, and extra, and interpreting their meanings for effective optimization.
This article explains the purpose of database normalization, outlines its benefits and drawbacks, and walks through a concrete demo that transforms an unnormalized employee table through the first, second, third, BC, and fourth normal forms, illustrating each step with diagrams.
Formatting SQL statements doesn't speed up execution, but it dramatically enhances readability, maintenance, and development efficiency, making testing easier and reducing errors through clear alias usage, leading commas, and line‑by‑line JOIN placement, as illustrated by practical examples.
This article compares SQL and NoSQL databases, outlining their core characteristics, advantages, and trade‑offs, and demonstrates practical schema designs for contact lists, social networks, and warehouse management, helping developers decide which technology best fits their project requirements.
This article explains why replacing OR conditions with UNION queries in MySQL often yields faster execution, using a concrete users table example, EXPLAIN analysis, and a comparison of type and ref values to illustrate the performance advantage of UNION over OR.
This article compares traditional relational tables with document‑oriented NoSQL, showing how JSON‑based storage simplifies handling multiple phone numbers, emails, addresses, and evolving contact data without schema changes or complex joins.
An in‑depth guide shows how to illustrate various SQL join types—including inner, full outer, left outer, and cross joins—using Venn diagrams and concrete table examples, highlighting result sets, null handling, and the pitfalls of Cartesian products for large tables.
This article explains two key relational database design techniques: using auto‑increment or UUID physical primary keys for foreign key relationships, and employing optimistic locking with a version column to safely handle concurrent updates, while also covering when locks can be omitted.
This guide explains how to use MySQL's show processlist command, interprets each column and common state values, and offers practical tips for diagnosing and resolving performance problems such as sleeping connections, locked queries, temporary tables, and query cache issues.
This article explains why using a physical primary key for foreign‑key relationships, how to implement optimistic locking with a version column for concurrent updates, and when it’s safe to update records without any locking, providing practical SQL examples for reliable database design.
This comprehensive guide explores MySQL's unique characteristics, hardware and OS tuning, configuration tweaks, schema and SQL optimization techniques, and provides actionable recommendations and tools to identify and resolve common performance bottlenecks.
This article chronicles the historical shift from early hierarchical data models to relational SQL, the rise of NoSQL, and the emergence of NewSQL solutions like Alibaba's DRDS, illustrating key concepts with diagrams, code examples, and a detailed Q&A.
This article explains the step‑by‑step order in which MySQL processes a SELECT statement, detailing how each clause creates a virtual table that feeds into the next operation until the final LIMIT result is returned to the user.
This article details a multi‑stage performance overhaul of a large‑scale chat application's SQLite database, covering trace‑based I/O analysis, view caching with Fragments, table‑splitting experiments, index size reduction, and the development of a PageTracer tool to quantify page‑level gains.
This guide shows how to use MySQL's information_schema tables to retrieve the total size of a database, individual table sizes, index sizes, and detailed per‑table statistics such as row count and combined data and index space, using concise SELECT statements.
MySQL’s Query Cache stores the results of SELECT statements by hashing the query, keeping the result set in memory, and invalidating cached entries whenever related tables change, allowing identical queries to be served instantly without re‑execution.
This article explains why GROUP BY requires sorting like ORDER BY, how a suitable index can satisfy both operations, and outlines the precise conditions—including single‑table queries, left‑most index prefixes, constant values, and limited aggregates—that allow MySQL to execute GROUP BY using an index scan.
Learn practical techniques to reduce nested loop iterations, prioritize inner-loop optimization, ensure join columns are indexed, and configure join buffers effectively, so your SQL joins run with minimal resource consumption and maximum performance.
MySQL uses only the Nested Loop Join algorithm, iterating each row of the left table and comparing it with every row of the right table, and when multiple tables are involved the intermediate results are repeatedly joined, making proper indexing crucial for performance.
This article presents a comprehensive set of MySQL performance optimization techniques for web applications, covering query caching, EXPLAIN analysis, LIMIT usage, indexing strategies, join type consistency, avoiding ORDER BY RAND(), selective column retrieval, proper primary keys, ENUM usage, prepared statements, unbuffered queries, IP storage, fixed‑length tables, vertical partitioning, batch deletions, column size reduction, storage engine selection, ORM benefits, and persistent connections.
This article outlines eight key MySQL index usage restrictions, including key length limits, unsupported data types, lack of functional and hash indexes, and conditions that prevent the optimizer from leveraging indexes, helping developers avoid performance pitfalls.
This article examines two database schema designs for a simple discussion forum, compares their table structures, highlights trade‑offs in query performance and storage, and explains why splitting tables can reduce data reads and improve overall efficiency.
This article reviews the evolution of big‑data management technologies, discusses the criteria for choosing appropriate big‑data benchmarks, compares existing benchmarks such as MapReduce tests, YCSB, BigBench and BigFrame, and provides an in‑depth analysis of the TPC‑DS benchmark and its certification status.
Discover twelve practical MySQL techniques—from avoiding dump file edits and tuning MyISAM block size to using EXPLAIN, query caching, and secure root password changes—that can boost performance, prevent data loss, and streamline database management for developers.
This article explains four situations where MySQL will ignore indexes—using a non‑leading column in a composite index, leading wildcards in LIKE queries, OR conditions with non‑indexed fields, and unquoted string literals—offering guidance to improve query performance.
This article compares MySQL client commands and features with their PostgreSQL psql equivalents, covering general command‑line options, SQL‑level operations such as showing tables, creating tables, listing databases, using databases, handling auto‑increment, process lists, global variables, and function‑level differences, plus practical code snippets.
This guide explains how to view MySQL temporary table statistics, interpret key metrics such as created_tmp_tables and created_tmp_disk_tables, and adjust tmp_table_size to keep temporary tables in memory, thereby improving query performance and reducing disk I/O.
This article explains the three types of SQL joins—inner, outer, and cross—detailing their variations such as equi‑join, non‑equi‑join, natural join, left/right/full outer joins, and provides clear example queries and result illustrations to help readers understand how to combine tables effectively.
Using LIMIT OFFSET for pagination becomes inefficient beyond thousands of rows, but by leveraging indexed ID ranges, delayed joins, or index‑only scans you can retrieve pages in milliseconds, dramatically improving performance for large datasets.
This article explains why MySQL ORDER BY may trigger a filesort when no suitable index exists, demonstrates the issue with a product query, and shows how adding a composite index on category_id and price lets the database return rows already sorted, removing the need for an extra sorting step.
This comprehensive guide explains what databases are, their evolution, key models, core DBMS components, and provides step‑by‑step instructions for installing, configuring, and securing MySQL on Linux, along with essential SQL commands and concepts.
This article provides a comprehensive overview of MySQL/MariaDB, detailing its architecture, core components, server and client programs, command-line options, data types, SQL language parts, metadata, and variable management, offering practical guidance for developers and administrators.
This article explains how to protect user privacy by using PostgreSQL's built-in pgcrypto extension to encrypt and decrypt sensitive fields such as phone numbers and ID numbers, showing practical SQL examples for creating tables, altering columns, storing ciphertext, and querying encrypted data.
This article compares two SQL counter queries, presenting a detailed MySQL version with CASE statements and variable counters alongside a concise PostgreSQL version using ROW_NUMBER, and includes an explanation of the differences and a brief author biography.
The article analyzes a slow PostgreSQL query that joins multiple tables to fetch flight numbers, compares several alternative query formulations with their execution plans, and presents a final solution using array aggregation to achieve sub‑millisecond performance despite existing indexes.
This article examines how to efficiently query PostgreSQL tables that store comma‑separated city names by comparing plain OR conditions, regular‑expression operators, and array‑based searches with GIN indexes, showing speed measurements and validation of result consistency.
The article demonstrates how to split a space‑separated "validperiods" column into separate begin, end, and price fields in PostgreSQL using string_to_array, to_timestamp, regexp_replace, and unnest to transform the data into a readable tabular format.
This article explains how to transform a non‑visible 0x03‑separated city string stored in a PostgreSQL table into a sorted array so that routes with the same set of cities can be grouped and counted efficiently.
This article explains how to count paid and unpaid orders per user‑product pair in PostgreSQL by aggregating order statuses into arrays, using a sub‑query with GROUP BY and a CASE expression to produce accurate payment success and total order counts.
This article explains how to leverage PostgreSQL's unnest and string_to_array functions to split a comma‑separated 'arrive' column and efficiently query for popular island destinations such as Maldives, Phuket, Bali, and others without writing complex regular expressions.
The article explains how to calculate the total number of possible flight‑hotel combinations for travel packages by defining a custom multiplicative aggregate function in PostgreSQL, provides the SQL code, demonstrates its usage with examples, and discusses its practical advantages for flexible grouping.
This guide outlines a comprehensive learning path for SQL, recommending essential books for beginners, advanced users, and operations, while highlighting key concepts such as EXISTS vs JOIN, row‑column transformations, and capacity planning, plus additional resources like podcasts and blogs.
This article explains the root causes of MySQL character‑set garbling, illustrates the encoding/decoding steps during data insertion and retrieval, and provides practical methods—including dump‑reload and binary conversion—to prevent and repair corrupted text.
Learn how to monitor and optimize MySQL server performance by examining critical variables, status metrics, and configuration parameters such as slow query settings, connection limits, key buffer size, query cache, temporary tables, and thread usage to achieve stable, efficient database operation.
This guide explains how mysqldump works, its syntax, and practical commands for backing up all databases, specific databases or tables, exporting only DDL, handling large datasets, compressing backups, and restoring data on Linux or Windows environments.
The article explains how inefficient SQL statements increase logical reads, why creating effective indexes dramatically reduces those reads and execution time, and discusses practical guidelines for choosing indexed columns, differences between clustered and non‑clustered indexes, and common pitfalls in index design.
This guide walks through essential Oracle database restore steps, from checking the current directory and copying dump files to restarting the service, managing users, creating directories, and importing data with impdp commands.
This guide explains how to monitor MySQL connection counts per IP by using a SQL query on information_schema.processlist and shell commands that parse the process list, providing a clear method to track each client’s connections.
This article provides concise MySQL Q&A covering how to retrieve server version and database name, filter records with NOT operators, combine AND/OR conditions, use IFNULL, limit query results, choose between Oracle and MySQL, obtain the current date, export tables to XML, understand persistent connections, list table indexes, and explain CSV tables.
This guide shares a curated list of foundational books and resources for beginners aiming to master SQL, deepen MySQL expertise, and develop the operational and data‑centric mindset needed for a successful DBA career, including links to downloads and a weekly MySQL radio show.
This article examines the ongoing debate between SQL and NoSQL databases for big‑data projects, presenting expert arguments on performance, scalability, standardization, and flexibility to help enterprises decide the optimal solution.
MariaDB 10’s multi-source replication lets a single slave pull data from multiple masters, enabling centralized querying, backup, and data consistency checks across disparate databases—ideal for OLAP, big-data aggregation, and simplifying DBA operations.
This article explains how to build a minimal database by storing records in a fixed‑length text file, using B‑tree structures for efficient lookup, creating index files for non‑primary fields, and outlines additional features such as SQL parsing, joins, transactions, backup and remote access.
This article presents a comprehensive side‑by‑side mapping of 45 common SQL Server functions to their Oracle equivalents, covering numeric, string, date, and miscellaneous operations with example queries and notes on usage differences.
A MySQL slow‑query caused 100% I/O usage due to a massive temporary table, but by splitting the original SELECT into two statements—first fetching IDs then retrieving full rows—the execution time dropped from several seconds to under a second and I/O fell below 1%.
