This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication, covering the underlying principles, master and slave configuration files, user creation, data backup, log synchronization, and verification with example commands and code snippets.
This guide provides step‑by‑step Oracle commands for creating and dropping databases, configuring users and tablespaces, performing full and table‑level backups with expdp/impdp, handling version compatibility, and cleaning up residual files and sessions.
This article compiles 52 practical SQL performance‑tuning strategies—including index best practices, query rewrites, use of temporary tables, proper data types, and server configuration tips—to help developers dramatically speed up MySQL and other relational database queries while avoiding common pitfalls.
This article compiles 52 practical SQL optimization strategies—including index usage, query rewriting, avoiding full table scans, proper data types, and server configuration tips—to help developers dramatically improve query speed and overall database performance.
This guide explains MySQL's three levels of user permissions, shows how to create accounts with various host specifications, demonstrates how to delete users, list existing accounts, and display a specific user's granted privileges using SQL statements.
This article presents five practical rules—return only needed rows, use proper indexes, avoid subqueries, replace OFFSET pagination with key‑based limits, and understand SQL's logical execution order—to help developers write efficient, high‑performance SQL queries across major relational databases.
This article introduces MySQL index basics, covering index concepts, advantages and disadvantages, various index types such as B‑Tree, B+Tree, hash, and full‑text, their storage structures, creation syntax, left‑most prefix and covering indexes, and practical design guidelines for effective indexing.
This guide explains how to become a professional software tester by covering test case creation, essential testing methods, protocol basics, when to adopt automation, key testing tools, UI and API automation scripting, SQL proficiency, and Linux basics for effective testing workflows.
This article explains why SQL query optimization matters and presents five practical rules—return only needed rows, use the right indexes, avoid subqueries, replace OFFSET‑based pagination, and master the logical execution order—to help developers write faster, more efficient database queries across major relational systems.
This guide explains how Flyway automates database schema migrations using version‑controlled SQL scripts, covering its core concepts, naming conventions, command‑line usage, Maven integration, and seamless Spring Boot configuration to keep databases in sync with application releases.
This article presents a comprehensive collection of 52 practical SQL performance optimization techniques, covering indexing, query rewriting, use of EXISTS, avoiding full table scans, proper handling of NULLs, OR/IN clauses, temporary tables, storage engine choices, backup procedures, and other best practices to improve database efficiency.
This article presents 21 practical SQL habits covering safe delete/update usage, testing changes, backups, transaction handling, performance tuning with EXPLAIN and indexes, concise SELECT statements, proper data types, batch operations, and clean coding conventions to make your database work more reliable and efficient.
This article presents five practical rules for optimizing SQL queries—including selecting only needed columns, ensuring proper indexes, avoiding subqueries, using keyset pagination instead of OFFSET, and understanding the logical execution order—along with concrete examples and execution‑plan analysis for major relational databases.
This article explains a real‑world MySQL GROUP_CONCAT truncation problem caused by the default length limit, demonstrates how it affected a doctor‑scheduling system, and provides two practical solutions: increasing the group_concat_max_len setting or adjusting the business logic to reduce concatenated data.
This article explains five common issues caused by NULL values in MySQL columns—data loss with COUNT, DISTINCT, and inequality queries, NULL‑induced NULL results in SUM leading to NPE, increased query complexity, and index behavior—while providing practical SQL solutions and best‑practice recommendations.
This article provides a comprehensive overview of database system design, covering its historical evolution, classification of relational and NoSQL databases, key architectural patterns, consistency models, indexing techniques, storage formats, compression methods, and practical considerations for selecting the right database solution.
This article walks through a MySQL 5.6 scenario where a query that originally took over 30,000 seconds is optimized using proper indexing, sub‑query to join conversion, and composite indexes, demonstrating dramatic performance gains and offering practical tips for future query tuning.
Learn how to design a simple book management database, define Django models for authors, publishers, books and their many‑to‑many relationships, and perform common ORM operations such as retrieving all records, filtering with conditions, using first(), get(), and inspecting the generated SQL.
This article explains the challenges of returning large result sets in APIs, compares traditional offset‑based pagination with cursor‑based approaches, shows performance differences through SQL examples, and discusses why cursor pagination is more efficient for deep paging and concurrent data modifications.
This article explains the concept of historical chain (slowly changing dimension) tables in data warehousing, demonstrates how to create source and target tables, provides a PL/pgSQL stored procedure to handle inserts, updates, and deletions, and shows step‑by‑step testing with sample SQL scripts.
This article demonstrates how to create two MySQL tables, populate them with sample data, and use various SELECT statements—including inner joins, filtered joins, and UNION operations—to retrieve combined information across the tables.
This guide lists useful MySQL client shortcuts, explains the help command usage, provides common mysqladmin administration examples, and shows how to diagnose high CPU usage by tracing the offending SQL statements through process and performance schema queries.
This comprehensive guide explains MySQL's server and storage engine layers, SQL execution order, BinLog/RedoLog/UndoLog mechanisms, index structures and design principles, transaction isolation levels, lock types, MVCC, buffer pool behavior, table slimming, join strategies, statistics, random queries, and practical optimization techniques for high‑performance database operations.
This article explains why MySQL uses B+‑tree indexes, describes the principles of B‑tree and B+‑tree structures, compares MyISAM and InnoDB index implementations, and provides practical optimization tips such as using auto‑increment primary keys, the left‑most prefix rule, and proper configuration settings.
This article presents a thorough overview of MySQL performance tuning, covering index usage, query rewriting, SELECT statement ordering, DML batch operations, hint directives, and table design recommendations to help developers write efficient SQL for large‑scale systems.
This article explains how proper index creation boosts MySQL query performance, detailing the mechanics of indexes, why B+Tree is chosen over binary and balanced trees, the storage differences between MyISAM and InnoDB, and practical guidelines for designing effective single‑column, composite, and covering indexes.
This article explains data warehouse fundamentals, reviews classic warehouse models such as ER, dimensional, Data Vault and Anchor, then dives deep into dimensional modeling concepts, star and snowflake schemas, and demonstrates a practical e‑commerce scenario with SQL examples and trade‑offs.
This article explains the purpose and usage of MySQL character set system variables, illustrates how they affect client‑server communication and file handling, and provides practical examples and diagrams to help developers correctly configure character sets for optimal performance.
This article presents five practical rules for writing efficient SQL queries—including selecting only needed rows, using proper indexes, avoiding subqueries, replacing OFFSET pagination, and understanding the logical execution order—supported by examples, execution plans, and performance tips for various relational databases.
This article explains how to build a real‑time data processing platform using Flink, covering the Lambda architecture, design approaches, SQL and custom‑Jar task definitions, UI drag‑and‑drop, cluster resource management on Yarn and Kubernetes, submission modes, scheduling, permission and metadata handling, logging, and monitoring with Prometheus and Grafana.
This article explains the fundamentals of SQL parsing, covering lexer and parser mechanisms, context‑free grammar concepts, LL(1) and LR(1) parsing techniques, and demonstrates how Apache ShardingSphere implements a flexible, multi‑dialect SQL parser with features such as formatting, parameterization, and extensibility.
This article presents five practical rules for writing efficient SQL queries—including selecting only needed rows, using proper indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑set pagination, and mastering the logical execution order of SQL clauses—to help developers and DBAs improve query performance across major relational databases.
This article explains MyBatis dynamic SQL features—including if, choose, trim, foreach, and bind tags—by showing how to set up a Maven project, create a student table, write mapper methods, and run tests that demonstrate conditional queries, updates, inserts, batch operations, and database‑agnostic expressions.
Even when a MySQL query uses an index, it can still become a slow query; this article explains index structures, explains why full‑index scans and poor selectivity cause performance issues, and presents step‑by‑step optimization techniques such as index condition pushdown, virtual columns, and better filtering.
This article introduces MyFlash, an open‑source MySQL flashback utility, explains its limitations, provides step‑by‑step installation commands, details the available command‑line options for specifying databases, tables, time ranges and SQL types, and demonstrates a complete rollback test case with binlog parsing and execution.
This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.
This article explains the two forms of SQL CASE expressions, demonstrates how to use CASE for concise data aggregation and conditional updates, and provides practical examples with customer and order tables to simplify queries and avoid pitfalls.
The author shares his personal journey from first encountering Flink to becoming an Apache Flink Committer at ByteDance, detailing community involvement, code contributions, bug fixes, lessons learned, advice for newcomers, and concluding with promotional offers for Flink services.
This article explains how MySQL's InnoDB engine applies row‑level locks for DELETE and SELECT statements under various index configurations and isolation levels, covering MVCC, snapshot vs current reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.
This guide provides comprehensive Oracle database commands for creating and deleting databases in 11g/12c, configuring users and tablespaces, and performing full or selective backup and restore operations with Data Pump, along with practical tips for managing sessions, processes, and cleanup.
This article provides concise, interview‑style explanations of MyBatis core concepts—including the difference between #{} and ${}, additional XML tags, DAO interface mechanics, pagination, plugin development, batch operations, executors, lazy loading, enum mapping, and internal configuration mapping—helping developers ace backend Java ORM interviews.
The DBLE 3.20.10.0 release notes detail the open‑source distributed middleware's project overview, major enhancements such as management‑port metadata access and read/write splitting, a comprehensive list of new features, bug fixes, backward‑compatibility changes, and references to past versions and documentation.
This article explains MySQL primary key design principles, compares auto‑increment, UUID and uuid_short approaches, provides SQL examples and performance benchmarks, and offers guidance on choosing business‑related versus unrelated key columns for optimal data integrity and query efficiency.
This article explains the hidden costs of using SELECT * in MySQL queries, explores how unnecessary columns, large data types, and loss of covering‑index opportunities degrade performance, and shows how composite indexes and proper indexing strategies can dramatically speed up data retrieval.
This guide explains how to filter data in MySQL using various query conditions such as WHERE, comparison operators, BETWEEN, IN, NULL checks, logical operators, short‑circuit evaluation, and operator precedence, with clear examples and visual illustrations.
This article examines MySQL pagination strategies for large tables, detailing preparation, general LIMIT‑based pagination, subquery and ID‑range optimizations, performance test results, and practical recommendations for improving query speed on millions of rows.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
This article presents five practical rules for writing efficient SQL queries—including using precise WHERE clauses, creating appropriate indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑based limits, and understanding the logical execution order of SQL clauses—to help developers improve database performance across major relational systems.
This article provides an extensive overview of MySQL, covering database creation, renaming, and deletion, table creation and alteration, column and index management, data queries, inserts, updates, deletes, operator usage, view handling, functions, stored procedures, triggers, sequence generation, user management, and permission configuration, with practical examples and code snippets.
This article explains Change Data Capture (CDC) and how Flink CDC can be used for incremental data synchronization, real‑time materialized views, audit logging, and CDC‑based joins, providing code examples, Maven dependencies, and SQL/Java snippets for MySQL and Kafka integrations.
This article explains how MyBatis loads its global configuration, parses mapper files, builds SqlSessionFactory and SqlSession, and executes SQL statements through a series of internal components such as Executor, StatementHandler, and caches, illustrated with detailed code snippets.
The article introduces the JUST engine, a spatio‑temporal data platform that extends GeoMesa with three new indexes (Z2T, XZ2T, time_range), defines nine common and three specialized data models, provides default indexing strategies, and offers detailed SQL usage guidelines for efficient querying of massive urban datasets.
The article explains why using COUNT(*) for existence checks is inefficient, proposes using SELECT 1 with LIMIT 1 in SQL and a null‑check in Java, provides code examples, and highlights the performance improvements of this optimization.
This article explains MySQL 8’s newly supported CHECK constraints, outlines usage restrictions, and provides step‑by‑step examples—including simple age validation and complex gender‑age logic—showing how to create, test, alter, and drop constraints while highlighting practical considerations.
This article explains how Suning built a massive user‑tag platform using ClickHouse and RoaringBitmap to enable second‑level user profiling for over 600 million users, covering tag generation, storage, query architecture, and performance optimizations for marketing campaigns.
This article explains MySQL's three lock levels—table, row, and page—describes next‑key, gap, and record locks, analyzes why deadlocks occur, and provides multiple real‑world examples and prevention strategies, including code snippets for reproducible scenarios.
This guide explains why MySQL queries become slow, how to diagnose issues with slow‑query logs, Explain and Show Profile, details the structure and types of indexes, interprets each column of the Explain output, and demonstrates practical optimization steps with real SQL examples.
The article explains how ThinkPHP’s query method works with raw SQL, distinguishes between execute for writes and query for reads, details MySQL master‑slave replication considerations, and describes the framework’s database connection initialization, including read/write separation logic and related code examples.
This article provides a comprehensive overview of the relational model and SQL, covering fundamental concepts, set‑based operations such as SELECT, WHERE, GROUP BY, UNION, and various JOIN types, along with practical code examples and explanations of integrity constraints and DML statements.
Flink SQL, the high‑level SQL interface for Apache Flink, offers language‑independent, dependency‑free, easy‑to‑use stream processing with advanced features such as DDL, UDFs, time semantics, windowing, pattern matching, and built‑in connectors, supporting data synchronization, batch‑stream fusion, Hive integration, and various product enhancements.
This guide explains how to set up Apache Zeppelin as an interactive notebook for Flink SQL, covering download, environment configuration, Zeppelin and Flink interpreter settings on YARN, Hive integration, and step‑by‑step testing of streaming SQL queries.
This article explains the functional and performance differences among MySQL's count(1), count(*), and count(column) functions, illustrates how NULL handling varies, and provides a concrete example demonstrating their results and execution efficiency under different table schemas.
Ctrip’s hotel data team tackled a 3 TB daily data load by building a ClickHouse cluster on VMware, creating custom sync and execution tools, applying query optimizations, and handling merge and memory errors, ultimately achieving over 400% performance gains across multiple reporting themes.
This article walks through five common data‑cleaning scenarios—dropping/renaming columns, handling duplicates and nulls, string manipulation, merging tables, and window‑function ranking—showing practical SQL and Python code that can be applied to large‑scale data warehouses.
This article explains how MySQL's CASE WHEN, IF, and IFNULL functions can be used to implement conditional logic in SQL queries, providing syntax, examples, and combinations with aggregate functions for common data‑analysis scenarios.
The article explains how Flink 1.11 deepens its integration with Hive, covering background, new connector features, simplified dependency management, enhanced Hive dialect, streaming writes and reads, temporal table joins, and how these capabilities enable a unified batch‑streaming data warehouse.
This article compiles 100 common MySQL interview questions covering index structures, hash vs B‑tree trade‑offs, clustering, transaction isolation levels, lock types, primary key choices, storage engines, data types, binlog formats, pagination strategies, and best practices for schema design and query optimization.
This article examines common MySQL pitfalls—including signed vs. unsigned integers, auto‑increment key requirements, varchar length versus byte count, case‑insensitive collations, and storing emoji characters—providing concrete SQL examples, error screenshots, and configuration tips to help developers avoid unexpected errors.
This article explains how Hive determines the number of map and reduce tasks based on input file size and block configuration, discusses when to increase or decrease map counts, and provides practical commands for adjusting reducer settings to optimize large‑scale data processing.
This article demonstrates how to replace complex SQL search queries with a Redis‑based solution by caching intermediate result sets using sets and sorted sets, optimizing performance through multi‑command transactions, and adding pagination, offering a practical backend development pattern for high‑traffic e‑commerce search.
A production MySQL query that omitted its leftmost indexed column caused a full‑table scan, CPU overload, and a read‑only alert, prompting a rapid investigation, temporary index addition, and a deeper discussion of the left‑most matching principle, Java validation pitfalls, and best‑practice lessons.
DataGrip, JetBrains' database IDE, supports most major relational databases and offers an intuitive interface for configuring connections, executing queries, editing data, creating tables, exporting/importing data, and leveraging advanced features such as keyword navigation, code completion, and result set filtering, making database management efficient.
This article compiles essential MySQL interview questions covering data types, storage engines, locks, transactions, triggers, index creation and usage, join types, and practical SQL optimization techniques with concrete examples and code snippets for developers to master database fundamentals.
This guide explains how to identify bottlenecks and boost MySQL performance through both soft (query, index, schema) and hard (hardware, configuration, sharding, caching) optimizations, offering practical commands, best‑practice tips, and visual illustrations for each step.
This article explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, shows several SELECT queries to list duplicates, and provides multiple DELETE strategies—including sub‑queries and multi‑column handling—to safely remove excess records while keeping one copy.
This article compares MySQL's three data‑removal commands—DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, impact on disk space and auto‑increment values, and best‑practice usage with code examples and optimization tips.
This guide explains how MySQL handles string‑date conversions, shows when to use date_format for query conditions, warns about performance impacts, demonstrates converting query results with str_to_date, and clarifies the meaning of common format placeholders.
The article explains that using LIMIT with DELETE in MySQL improves safety, reduces lock time, prevents long‑running transactions, and enhances performance, and it provides syntax, examples, and expert opinions to illustrate the benefits of this habit.
This article explores eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, subquery updates, mixed sorting, EXISTS clauses, condition push‑down, early result narrowing, and intermediate result push‑down—and provides rewritten SQL examples that dramatically reduce execution time.
This article explains why adding a LIMIT clause to MySQL DELETE statements is a good habit, compares it with TRUNCATE, shows the correct syntax, outlines performance and safety advantages, and discusses practical strategies for deleting large numbers of rows.
This tutorial demonstrates how to leverage MyBatis dynamic SQL features—including if, choose, trim, foreach, and bind tags—to build flexible SELECT, INSERT, UPDATE, and DELETE statements with conditional logic, code examples, and test cases for Java backend development.
This article examines MySQL pagination on a multi‑million‑row table, compares plain LIMIT queries with subquery, ID‑range, and temporary‑table techniques, provides SQL examples and performance measurements, and offers recommendations for fast, scalable paging.
This article traces MySQL's four‑decade history, its acquisition by Sun and Oracle, the rise of MariaDB as a compatible fork, and provides a practical guide to installing and using MariaDB with screenshots and tool recommendations.
This guide walks you through installing the free, open‑source DBeaver client, configuring JDBC connections for databases such as PostgreSQL, and using its built‑in tools to create ER diagrams, covering essential steps, tips, and common pitfalls.
This article explains how MySQL indexes work, compares binary trees, B‑Tree and B+Tree structures, describes their implementation in MyISAM and InnoDB storage engines, and provides practical guidelines for creating efficient single‑column, composite, and covering indexes to improve query performance.
This guide walks you through installing the MariaDB Python connector, establishing a secure connection, executing SELECT and INSERT statements, handling transactions and errors, and properly closing the connection, providing a complete workflow for Python developers working with MariaDB databases.
The article examines the difficulties of reusing SQL Server stored procedures to obtain simple boolean checks, illustrates a concrete example of counting jobs with temporary tables, discusses maintenance pitfalls, and concludes that business logic is often better implemented outside stored procedures.
This article traces the evolution of openGauss from its Huawei roots and systematically evaluates how well openGauss 1.0.0 supports Oracle’s various table types, providing creation examples, compatibility results, and guidance on work‑arounds for unsupported features.
This article presents a comprehensive collection of 50 SQL interview questions, complete with sample queries that demonstrate various operations such as joins, subqueries, aggregations, updates, and deletions on student, course, teacher, and enrollment tables.
This article explains the relational model, its data structures, set-oriented operations, and integrity constraints, then demonstrates core SQL clauses such as SELECT, WHERE, GROUP BY, UNION, INTERSECT, EXCEPT, and various JOIN types with clear examples and diagrams.
This article explains how to identify slow‑query symptoms, use system and SQL‑level indicators, apply various database tools, follow best‑practice writing tips, read execution plans, and walk through a complete MySQL optimization case to dramatically improve performance.
This article explains why SQL queries do not start with SELECT, outlines the typical logical execution order of clauses such as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, and discusses how database engines may reorder operations for optimization, with code examples and comparisons to LINQ and pandas.
This article explains MySQL 8.0 window functions, provides clear definitions, demonstrates basic and advanced usage with sample tables and SQL queries, and lists all available window functions, helping readers apply ranking and cumulative calculations in real‑world database scenarios.
This guide introduces DBeaver, a free open‑source Java‑based database management tool, covering its features, cross‑platform installation, how to create connections (e.g., PostgreSQL), test drivers, and generate ER diagrams, along with useful resource links.
The article explains the performance problems of using OFFSET and LIMIT for database pagination on large tables, demonstrates their inefficiency with real‑world examples, and presents cursor‑based pagination as a high‑performance alternative that leverages indexed primary keys.
This article explains the design philosophy of relational databases, describing the relational model, its components, and how SQL operates as a set‑oriented language with examples of SELECT, GROUP BY, UNION, JOIN and basic DML statements.
This article presents a comprehensive collection of SQL query optimization techniques, covering index usage, avoiding full table scans, proper handling of NULLs, functions, LIKE patterns, temporary tables, cursors, and other best practices to improve database performance and maintainability.
This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.
This guide explains the conditions under which MySQL can use indexes for range scans, LIKE patterns, BETWEEN clauses, and sorting, detailing key_len calculation, index key vs. filter vs. table filter, and practical examples to demystify index utilization.
This article explains how to use Amazon Redshift Spectrum to directly query Apache Hudi (and Delta Lake) tables stored in Amazon S3, covering supported formats, required DDL statements, partition handling, and common troubleshooting tips.
