This article walks through MySQL 8.0's key relational‑database enhancements, including invisible indexes for performance testing, the SET PERSIST command for persistent configuration, default utf8mb4 encoding, Common Table Expressions for clearer queries, and window functions for advanced ranking and aggregation.
This article shows how to identify and remove duplicate rows in a MySQL table by defining duplication, using GROUP BY with HAVING, creating temporary tables with MIN, and applying various techniques—including UNION, nested subqueries, and joins—to handle single‑column and multi‑column duplicate detection.
This guide explains why an Oracle DBMS_JOB can end up with a next execution date of 4000‑01‑01, how to interpret its status fields, diagnose the underlying errors, and restore the job using manual execution and log‑based troubleshooting steps.
This guide shows how to use SRVCTL commands and SQL queries to view the status of Oracle RAC databases, instances, services, node applications, ASM instances, and disk groups, as well as how to start and stop the entire RAC cluster safely.
This article explains how MySQL indexes work, why queries can become slow, how to interpret the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra columns—and provides practical examples and optimization cases to improve query performance while balancing the cost of maintaining indexes.
This guide walks through configuring Oracle multi‑master data replication, covering site creation, group objects, propagator and receiver setup, purge scheduling, database link creation, replication group definition, table registration, conflict handling, and finally resuming replication, all illustrated with complete SQL code examples.
This tutorial demonstrates how to install PyMySQL, establish a MySQL connection in Python, and perform table creation, data insertion, and query operations using example code, while outlining required prerequisites such as credentials and database details.
This article presents thirty practical SQL tuning guidelines—ranging from avoiding certain WHERE‑clause operators and functions to proper index usage and set‑based alternatives—that help prevent full table scans and improve overall database performance.
This article compiles essential MySQL interview topics, covering ACID transaction properties, isolation levels and their issues, storage engine differences, index types, query execution order, lock mechanisms, temporary tables, normalization, read‑write splitting, performance tuning, and recovery logs, providing concise explanations and examples for each concept.
This article investigates the classic MySQL pagination “optimization” technique, reproduces tests on a low‑end server, compares plain LIMIT queries with the sub‑query rewrite on both clustered and non‑clustered indexes, and explains why the method sometimes improves performance and sometimes adds overhead.
The article explains that Oracle Enterprise Manager requires listeners with dynamic registration, shows how a static listener on a non‑default port causes a "database is down" error, and provides step‑by‑step commands to modify the local_listener parameter so both listeners register dynamically and EM works correctly.
This article explains the challenges of MySQL SQL auditing, compares semi‑automated and manual review methods, and introduces Inception—a MySQL‑compatible automated auditing, execution, and rollback system that addresses many of the pain points faced by DBAs.
Both MySQL 8 and MariaDB 10.2 introduce robust window functions and Common Table Expressions (CTEs), enabling developers to write more efficient, maintainable queries; this article explains their syntax, demonstrates practical examples, compares implementations, and highlights performance and readability benefits across popular database systems.
This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.
This article outlines the most frequent SQL performance mistakes—such as misusing UPDATE, over‑selecting columns, unnecessary double queries, and improper use of GUIDs—and provides concrete, actionable techniques like CASE expressions, temporary tables, batch operations, and table‑valued functions to dramatically improve query speed and concurrency.
This guide explains Oracle's TX row lock mechanism, shows how to identify blocking sessions, locate the exact locked rows using system views and ROWID functions, and provides practical steps to prevent and mitigate TX lock waiting issues.
By switching to the InnoDB storage engine, allocating about 80% of system memory to its buffer pool, and configuring multiple buffer pool instances, you can significantly improve MySQL performance, with practical commands and guidelines for various memory sizes.
This comprehensive guide introduces MySQL fundamentals, walks through installation on Windows and Linux, explains core commands for connecting, creating databases, managing users and privileges, performing table CRUD operations, handling data types, and provides a curated list of analysis, backup, performance, HA, and GUI tools for MySQL.
This article traces the evolution of database systems—from early navigational models and Codd's relational theory through the rise of NoSQL and sharding, to the resurgence of NewSQL and the emerging HTAP paradigm—highlighting technical motivations, design trade‑offs, and future directions.
This article explains how Alibaba built a massive real‑time computing platform using Flink and its Blink extensions, detailing the challenges of ultra‑low latency, exactly‑once guarantees, and high throughput, and showing how these technologies powered the record‑breaking Double 11 shopping festival.
This article presents five practical MySQL index usage questions, each with multiple‑choice options, followed by detailed answers and explanations that illustrate when an index is appropriate, how function usage affects it, and the impact on query performance.
A 2017 roundup highlights ten innovative database releases—including a time‑series extension for PostgreSQL, a multi‑model Azure service, Google’s globally distributed Spanner, Amazon’s Neptune graph service, and several open‑source cloud‑native databases—detailing their key features, architectures, and intended use cases.
This article explains MySQL character sets and collations, their relationship, how to view supported options, and step‑by‑step methods for configuring them at server, database, table, and column levels, plus guidance on when each setting should be applied.
This article explains the four MySQL transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—by creating a sample table and running step‑by‑step command‑line sessions that demonstrate dirty reads, non‑repeatable reads, phantom reads, and the practical limits of InnoDB's MVCC implementation.
This article shares practical experience deploying MariaDB ColumnStore for offline OLAP, covering its MPP‑based architecture, user‑experience benefits, high‑availability options, numerous syntax and data‑type limitations, and detailed scripts for schema conversion and incremental data extraction using pt‑archiver.
This article explains how PostgreSQL transforms and rewrites query trees, why a completed transform‑rewrite tree may not be optimal, and details the subquery_planner’s role, tuple_fraction handling, and the step‑by‑step logical optimization process used to generate efficient execution plans.
This article explains the classification of Oracle data dictionary objects, demonstrates how to query static and dynamic dictionary tables and views, lists common view families and permission views, and provides numerous SQL examples for inspecting users, tables, indexes, sequences, constraints, and PL/SQL objects.
This article walks through the problem of duplicate rows in a MySQL table, shows an initial complex SQL attempt, compares a slow PHP‑based cleanup, and finally presents a concise, three‑step DELETE statement that eliminates duplicates in under a second.
This article explains why conventional indexes waste space and perform poorly on highly skewed columns, introduces a decode‑based function index that excludes high‑frequency values, details the experimental setup with millions of rows, compares index size and query performance, and outlines the method's limitations.
Learn a comprehensive methodology for SQL performance tuning, from identifying resource bottlenecks and understanding query processing stages to applying practical optimization tactics such as efficient joins, index usage, avoiding costly operations, and best‑practice guidelines that improve execution speed and resource consumption.
An Oracle SQL query with five EXISTS permission checks runs dramatically slower when only a few permissions are configured, and the author investigates the root cause using execution plans, SQL Monitor data, and step‑by‑step testing, ultimately revealing how execution counts and row returns drive the unexpected performance degradation.
This article introduces MySQL 8.0.2's window functions, demonstrates how to compute yearly average ratings and rank movies by year and overall using examples with sample tables, and explains the syntax and benefits over traditional GROUP BY queries.
This article outlines essential database design principles and a comprehensive set of query optimization techniques, covering row size limits, appropriate data types, field length choices, index usage, avoiding costly WHERE clause patterns, and best practices for temporary tables, transactions, and result set handling to boost SQL performance.
This guide explains the factors that affect MySQL INSERT performance and presents a collection of practical techniques—including multi‑row INSERT, bulk_insert_buffer_size tuning, INSERT DELAYED, LOAD DATA INFILE, table creation with disabled keys, LOCK TABLES, and the proper use of IGNORE and ON DUPLICATE KEY UPDATE—to dramatically speed up large‑scale data loading.
This article explains the importance of upgrading PostgreSQL to benefit from new features and security fixes, compares traditional upgrade methods, introduces the PGQ replication system, and provides a detailed step‑by‑step guide for performing a major version upgrade with minimal downtime.
This guide walks you through using MySQL's native JSON data type—creating tables with JSON columns, inserting simple and complex JSON values, updating nested structures, generating indexed columns from JSON fields, converting strings to JSON, and a comprehensive list of built‑in JSON functions.
The article explains how NetEase’s real‑time big‑data stream computing platform, Sloth, handles massive, continuously generated data during China’s Double 11 shopping festival, covering use cases, architectural shifts from batch to incremental processing, technical challenges, and the role of stream‑SQL for easier development.
The article explains how continuous data generation and real‑time stream processing enable e‑commerce platforms like NetEase Kaola to handle massive Double 11 traffic, showcasing use cases, architectural shifts from batch to incremental computing, and the technical challenges of latency, accuracy, and fault tolerance.
This guide presents thirty‑four practical Oracle SQL optimization techniques—from choosing the most efficient table order and rewriting WHERE clauses to leveraging indexes, avoiding SELECT *, using DECODE, and replacing costly operations—helping developers dramatically improve query speed and resource usage.
This article examines 2017 GitHub data to compare how major tech companies like Microsoft, Google, Amazon, and others contribute to open‑source projects, revealing surprising rankings, star counts, and the methodology behind the analysis using Google BigQuery and custom SQL queries.
The article examines why a MySQL DELETE statement with a composite index on biz_date and status still triggers a full‑table scan, shows EXPLAIN output, tests forced index usage, and ultimately demonstrates that narrowing the date range in the query dramatically reduces rows examined and improves performance.
Facing a last‑minute performance crisis on a major release, the author dissected eight SQL statements, merged four permission‑related queries using Oracle's RANK function, and reduced the responsibility‑lookup time from five seconds to under one second, ensuring the system met its strict latency target.
The article introduces data-ci, a SQL‑driven unit testing framework that lets engineers write, organize, and automate data validation tests for ETL pipelines, providing assertions, failure callbacks, coverage reporting, and CI integration to improve data quality and reliability.
This guide explains MySQL B+Tree index structures, their advantages, how to create primary, unique, ordinary, full‑text, composite and prefix indexes, and provides practical rules and tips for designing efficient indexes, query patterns, and sorting strategies to dramatically improve database performance.
This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
This guide provides a comprehensive collection of Oracle SQL commands covering connection, query execution, variable handling, formatting, tablespace creation, modification, and external table operations, presented step‑by‑step for database administrators and developers.
When a sales employee's workflow records vanished after a WeChat ID change, the team traced the deletion to MySQL binary logs, extracted the relevant log entries, and used bulk UPDATE statements to replace the old user_id with the new one, fully restoring the missing data.
In a high‑pressure performance‑optimization project, a team dissected a massive, multi‑with‑clause SQL report into smaller temporary‑table queries, applied dimensional modeling, and achieved a 6‑second runtime reduction while handling complex reporting requirements across regions, contractors, and milestones.
The article compiles Meituan‑Dianping engineers' Q&A covering SQL book suggestions, relational versus NoSQL engine choices, performance tuning techniques, time‑series database uses, sharding versus partitioning strategies, query handling in distributed systems, proxy limitations, and practical advice on replication and pre‑database setups.
This article provides a detailed, side‑by‑side comparison of MySQL and PostgreSQL, covering their histories, licensing, feature sets, performance characteristics, extensions like PostGIS, indexing, clustering, transaction handling, and practical guidance on choosing the right database for specific workloads.
The article investigates why a seemingly indexed MySQL DELETE query still performs a full‑table scan, analyzes execution plans and statistics, demonstrates forcing the index, and finally rewrites the query to limit the date range, achieving a much faster operation.
This guide explains Oracle datafile concepts, how to query absolute and relative file numbers, create and add files to tablespaces, modify file size with autoextend or resize, change file availability, rename or move files, and safely delete files while observing key restrictions.
KSQL is an open‑source, distributed SQL engine for Apache Kafka that enables continuous, real‑time queries on streaming data, lowering the barrier for analysts to perform stream processing, monitoring, security checks, and analytics without writing code.
This article recounts a real‑world Oracle SQL performance case where a slow WM_CONCAT‑based query took over 30 minutes, the root cause was identified, and a rewritten SQL using window functions and proper aggregation reduced execution time to under three seconds.
This article introduces Flink’s background, architecture, and ecosystem, then demonstrates a step‑by‑step tutorial on using Flink SQL to clean and transform streaming data from Kafka, highlighting its advantages over Storm for real‑time ETL.
Learn practical techniques to reduce unnecessary I/O and CPU consumption in SQL queries, including narrowing scan ranges, using partition filters, optimizing joins, leveraging hints, bind variables, and proper use of MERGE, UPDATE, EXISTS/IN, pagination, temporary tables, and other performance‑tuning strategies.
This article recounts a year-long journey to become a data mining engineer, explaining the role’s value, required skills, tools such as Excel, Tableau, SQL, Python, Scala, Spark, and machine‑learning techniques, and offers practical steps for aspiring professionals.
The article explains how set‑based SQL queries outperform procedural, row‑by‑row approaches by showing concrete examples, AUTOTRACE statistics, and PL/SQL function alternatives, highlighting massive differences in consistent gets, recursive calls, and overall resource consumption for large production data sets.
After a disastrous ERP data loss caused by a careless drop, this article walks through the importance of backups and provides a detailed, practical MySQL recovery process using full backups, binlog extraction, and incremental restoration to retrieve lost tables.
This article compiles ten classic MySQL error scenarios—from connection limits and replication conflicts to charset issues and file‑open limits—explaining the root causes, diagnostic commands, and step‑by‑step solutions so beginners and seasoned DBAs can troubleshoot confidently.
This article teaches how to read and manipulate Oracle execution plans using hints, SQL rewrites, design features, and statistics tricks, providing step‑by‑step examples, common pitfalls, and practical scripts to achieve predictable performance without costly statistics collection.
This article shares five actionable SQL techniques—concatenating fields, removing duplicate rows, leveraging WHERE clauses, using COUNT flexibly, and selecting only necessary columns—to improve query readability and efficiency in relational databases.
At HBaseCon 2017 Asia, Alibaba showcased a series of groundbreaking HBase enhancements—including strong synchronous replication, SQL-on-HBase capabilities, cross‑cluster range data copy, and read/write path optimizations—that dramatically improve performance, reliability, and usability for large‑scale big‑data storage.
This article explains how Oracle's parallel processing feature can accelerate data‑intensive queries by configuring session, table, or hint‑based parallelism, shows the degree‑of‑parallelism formula, and presents experimental results that compare execution times under different settings.
This article lists ten classic MySQL error scenarios—from connection limits and replication conflicts to startup failures and charset issues—explaining each problem, showing the exact error output, and providing step‑by‑step commands and configuration tweaks to resolve them.
This article explains the fundamentals of Oracle SQL execution plans, how to read and interpret them, the role of statistics in plan selection, methods for gathering statistics, various techniques for obtaining execution plans, and practical tips for identifying inefficient SQL statements.
This article explains why relational database middleware is essential for scaling internet‑level workloads, describes the principles of horizontal sharding and distributed primary‑key generation, and provides a comprehensive overview of Sharding‑JDBC’s architecture, core modules, performance benchmarks, and future roadmap.
This article explains what an Oracle SQL execution plan is, how statistics guide the optimizer, various ways to retrieve plans, the classification of plan types, and practical tips for spotting and fixing inefficient plans using real‑world examples and code snippets.
Based on a 2017 Stack Overflow survey of 64,000 developers, this article explains why SQL remains the second‑most used programming language, detailing eight technical, community‑driven and practical reasons that keep relational databases indispensable despite the rise of newer technologies.
This article compiles a wide range of interview questions sourced from Glassdoor covering general topics, machine learning, statistics, programming, big‑data frameworks, SQL, and brain‑teasers, providing candidates with English translations and insights into the types of problems asked by companies such as Apple, Google, Microsoft, Uber, and many others.
This guide explains Oracle SEQUENCE objects—how they generate numeric IDs, the full CREATE SEQUENCE syntax, practical usage with NEXTVAL and CURRVAL, alteration options, querying via data‑dictionary views, and safe removal—complete with runnable SQL examples.
This article explains transaction isolation levels, dirty reads, and phantom reads, compares how major databases such as SQL Server, PostgreSQL, MySQL, Oracle, DB2, MongoDB, CouchDB, Couchbase, and Cassandra implement these concepts, and highlights the performance‑security trade‑offs of each isolation level.
This article explores three progressive implementations for a complex e‑commerce search interface, comparing a monolithic SQL solution, an optimized multi‑query approach, and a Redis‑based caching strategy that leverages sets and sorted sets for fast, scalable results.
This article walks through a real‑world Oracle pagination query that took over five seconds for just fifteen rows, analyzes why ORDER BY caused the slowdown, and demonstrates a step‑by‑step rewrite using a WITH clause and index‑driven access that reduces execution time to under one second.
This article walks through the concepts behind NoSQL databases, contrasts them with traditional SQL relational models, and provides a step‑by‑step Python implementation of a minimalist key‑value store, complete with command parsing, TCP/IP messaging, and code examples.
This article introduces Common Table Expressions (CTE) in SQL, explains how they improve query readability and enable writable temporary tables, demonstrates multi‑table modifications, advanced CRUD scenarios, implements a ring‑buffer using CTE, and discusses performance benefits and potential pitfalls.
This article walks through the step‑by‑step process of detecting, analyzing, and fixing DB2 lock‑timeout incidents in a production environment, covering configuration tweaks, script modifications, diagnostic file interpretation, and practical recommendations to prevent future deadlocks.
This comprehensive guide explains what a database is, distinguishes relational and non‑relational types, walks through MySQL client‑server architecture, details SQL syntax for creating, querying, altering, and dropping databases, tables, and data, and resolves common Chinese character‑set and collation issues with practical commands and examples.
A developer added a seemingly harmless filter to an Oracle SQL statement, causing the optimizer to switch the driving table and join method, which inflated execution time from 2 seconds to 90 seconds, prompting a deep dive into execution plans, indexing, and hint techniques.
Apache Kylin is an open‑source, distributed OLAP engine built on Hadoop that uses pre‑computed cubes to deliver sub‑second, high‑concurrency SQL queries on massive datasets, integrates with popular BI tools, offers a modular architecture, recent 1.5.x enhancements, and extensive deployment options.
This comprehensive SQLite tutorial walks beginners through what SQLite is, how to install it on Windows and Linux, essential syntax, CRUD operations, database objects, functions, and language-specific programming interfaces, providing step‑by‑step instructions and visual aids for practical mastery.
The article recaps Liulishuo engineers' experience at Spark Summit 2017, covering Spark 2.2's cost‑based optimizer, production‑ready Structured Streaming, deep‑learning support via UDFs, live demos recognizing James Bond, and insights from vendor booths and industry case studies.
This article explains why deterministic encryption like ECB is unsafe for sensitive data, introduces nondeterministic authenticated encryption, and provides a practical blind‑index technique with PHP and SQL examples for searchable encryption of fields such as Social Security numbers.
This guide provides comprehensive PostgreSQL best‑practice recommendations covering naming conventions, column design, constraints, index strategies, NULL handling, development standards, and administration tips to help users quickly adopt consistent and efficient database practices.
This article introduces SQLPad, a lightweight browser‑based SQL client that offers smooth autocomplete, instant table and column browsing, and query saving across multiple databases, and explains how to install and start it via npm for a more efficient SQL writing experience.
This article explains eight typical situations in which Oracle SQL statements cannot use indexes, illustrates each case with concrete examples and step‑by‑step rewrites, and offers practical solutions—including predicate adjustments, function indexes, and query‑transformation fixes—to improve database performance.
This article explains eight common reasons why Oracle SQL statements fail to use indexes, provides concrete rewrite techniques, shows how optimizer bugs can block index usage, and outlines practical steps for enforcing SQL development standards through automated audit tools.
This guide walks through creating an Oracle 12cR2 container database with CREATE DATABASE, explains OMF vs non‑OMF options, details the catcdb.sql/perl changes, resolves common Perl module errors, compares 10g/11g syntax differences, and provides practical fixes such as blocksize adjustments.
This article explains how Oracle's bind variable peeking influences execution plan selection, demonstrates the performance differences between full table scans and index scans with skewed data distributions, and provides practical methods—including 10046 events, v$sql_bind_capture, and AWR views—to capture and analyze bind variable values for better SQL tuning.
This article explains what NoSQL means, compares it with traditional SQL databases, and walks through creating a lightweight Python key‑value store with TCP/IP commands, illustrating schema design, indexing, query examples, and the full source code for a toy NoSQL system.
The article explains why a seemingly harmless delete statement in an Oracle RAC+DG environment can become extremely slow, covering bind variable peek, execution‑plan changes, histogram effects, AWR/ASH analysis, rolling invalidation, and clustering factor, and shows how to diagnose and mitigate each issue.
This article explains how to identify and analyze MySQL slow queries using tools like SHOW VARIABLES, EXPLAIN, mysqldumpslow, and profiling, and provides practical recommendations for configuring server variables, optimizing indexes, and improving query performance through systematic testing and best‑practice SQL design.
This article explains how Oracle uses various lock types—including DML, DDL, and internal locks—to protect shared resources, details the lock manager workflow, describes the three lock components and lock modes, and shows practical SQL examples of transaction handling and blocking detection.
This article provides a clear overview of the four main SQL query language categories—DDL, DML, DCL, and TCL—explaining their purposes, typical commands, practical examples, and why each is essential for effective database design and manipulation.
This article explains how to manage data conversion errors during Oracle data loading by using the new VALIDATE_CONVERSION function to identify problematic rows and the enhanced CAST (and TO_…) functions with ON CONVERSION ERROR defaults, providing step‑by‑step examples, SQL scripts, and practical tips for clean inserts.
This article explains how to use Oracle's driving_site hint to minimize network traffic in distributed DBLINK queries, demonstrates a banking case where execution time drops from over eight seconds to under one second, and provides step‑by‑step view‑based solutions for DML optimization.
This article explains Oracle's dynamic sampling feature, outlines typical scenarios such as temporary tables and complex filters, presents real CRM case studies with misestimated cardinalities, demonstrates step‑by‑step solutions using DBMS_STATS commands, and shows how 12c enhancements dramatically improve estimation accuracy for complex queries.
This article explains the logical and physical execution order of SQL statements, offers practical tips for returning only needed data, reducing redundant work, using temporary tables wisely, optimizing subqueries and indexes, and provides concrete code examples to improve query performance.
This article explains the root causes of MySQL character‑set mojibake, illustrates the encoding/decoding steps during data insertion and retrieval, and provides reliable methods to prevent and repair corrupted text.
SQLAdvisor, an open-source MySQL optimization utility from Meituan-Dianping, analyzes a given SQL statement—parsing its lexical structure, WHERE clauses, field selectivity, aggregations and multi-table joins—to automatically recommend missing indexes, supporting INSERT/DELETE/UPDATE/SELECT operations, filtering existing indexes, and inviting community contributions on GitHub.
