BestHub
Sign in
Tagged articles

SQL

2900 articles · Page 6 of 29
Shepherd Advanced Notes
Shepherd Advanced Notes
Feb 5, 2025 · Databases

Elegant Techniques for Group‑Then‑Sort and Top‑Record Retrieval in MySQL & Elasticsearch

The article walks through a common database requirement—grouping rows, sorting within each group, and extracting the first (or top N) records—by preparing sample data, comparing MySQL window functions, subqueries, and JOIN solutions, adding index optimizations, and demonstrating equivalent Elasticsearch aggregations, all backed by concrete performance measurements.

ElasticsearchMySQLPerformance Optimization
0 likes · 11 min read
Elegant Techniques for Group‑Then‑Sort and Top‑Record Retrieval in MySQL & Elasticsearch
21CTO
21CTO
Feb 4, 2025 · Big Data

Why Python Beats Java and Scala for Modern Data Engineering

The article compares Java, Scala, SQL, and Python for data‑engineering tasks, arguing that Python’s versatility, rich ecosystem, and ease of use make it the preferred language for both small‑scale and massive Spark workloads despite its performance trade‑offs.

SQLScalaSpark
0 likes · 7 min read
Why Python Beats Java and Scala for Modern Data Engineering
ITPUB
ITPUB
Feb 1, 2025 · Databases

How to Query Elasticsearch with SQL: A Step‑by‑Step Guide

This tutorial explains how to use Elasticsearch's built‑in SQL interface to replace complex Query DSL statements with familiar SQL syntax, covering environment setup, index creation, data insertion, and a variety of query examples such as filtering, ordering, grouping, aggregation, and date manipulation.

Data RetrievalElasticsearchSQL
0 likes · 8 min read
How to Query Elasticsearch with SQL: A Step‑by‑Step Guide
IT Services Circle
IT Services Circle
Jan 31, 2025 · Databases

Common MySQL Commands and Practical Usage Guide

This article provides a comprehensive, step‑by‑step guide to essential MySQL commands—including connection, process inspection, variable queries, lock information, transaction status, engine details, character set checks, import/export, index management, table creation, stored procedures, and view creation—complete with example code snippets for each operation.

AdministrationMySQLSQL
0 likes · 12 min read
Common MySQL Commands and Practical Usage Guide
MaGe Linux Operations
MaGe Linux Operations
Jan 29, 2025 · Databases

Master MySQL Log Management: From Error to Slow Query Logs

This guide explains MySQL’s error, general, binary, and slow query logs, covering default settings, how to enable or modify them, binary log formats and recovery procedures, as well as tools for analyzing slow queries, providing a comprehensive reference for database administrators.

Binary LogLog ManagementMySQL
0 likes · 11 min read
Master MySQL Log Management: From Error to Slow Query Logs
ITPUB
ITPUB
Jan 27, 2025 · Databases

Surviving Oracle’s Dark Ages: Lessons from Classic DBA Nightmares

The article recounts decades‑long Oracle DBA experiences, describing early‑era crashes, notorious ORA‑1591 and ORA‑600 errors, troubleshooting tactics, and how those hardships forged highly skilled DBAs while highlighting the evolution toward more mature database systems.

DBADatabase ErrorsOracle
0 likes · 9 min read
Surviving Oracle’s Dark Ages: Lessons from Classic DBA Nightmares
MaGe Linux Operations
MaGe Linux Operations
Jan 24, 2025 · Databases

Enable and Configure MariaDB Log Auditing with the Server_Audit Plugin

This guide walks through verifying the MariaDB server_audit plugin, installing it via configuration files or SQL, setting audit event variables, enabling logging, and restarting the MySQL service in a Kubernetes environment to achieve comprehensive query and connection auditing.

Database ConfigurationKubernetesLog Auditing
0 likes · 5 min read
Enable and Configure MariaDB Log Auditing with the Server_Audit Plugin
Architecture Digest
Architecture Digest
Jan 24, 2025 · Backend Development

Why Using 1=1 in SQL Is a Bad Habit and How to Write Cleaner Queries

This article explains why developers often insert the always‑true condition 1=1 in SQL, examines its potential performance and readability drawbacks, and demonstrates cleaner alternatives using MyBatis dynamic tags and Entity Framework to build conditional queries without unnecessary predicates.

Dynamic QueryEntity FrameworkMyBatis
0 likes · 7 min read
Why Using 1=1 in SQL Is a Bad Habit and How to Write Cleaner Queries
Alibaba Cloud Developer
Alibaba Cloud Developer
Jan 24, 2025 · Big Data

Master DataWorks Notebook: Interactive SQL & Python for Big Data Development

This guide walks you through setting up a personal DataWorks Notebook, performing interactive SQL development with engines like MaxCompute, creating Python visualizations, building ipywidgets for dynamic queries, and leveraging the AI‑powered Copilot to rewrite, explain, and comment code, all within a unified big‑data platform.

CopilotDataWorksInteractive Development
0 likes · 9 min read
Master DataWorks Notebook: Interactive SQL & Python for Big Data Development
Raymond Ops
Raymond Ops
Jan 23, 2025 · Databases

Mastering SQL Execution Order & Multi‑Table Join Optimization

This guide explains the exact sequence in which SQL statements are processed, offers practical tips for optimizing multi‑table joins, and provides comprehensive index recommendations and mnemonic rules to improve query performance and reduce resource consumption.

JOINQuery OptimizationSQL
0 likes · 8 min read
Mastering SQL Execution Order & Multi‑Table Join Optimization
Aikesheng Open Source Community
Aikesheng Open Source Community
Jan 21, 2025 · Databases

New and Changed Features in MySQL 9.2.0

MySQL 9.2.0 introduces the CREATE_SPATIAL_REFERENCE_SYSTEM privilege, JavaScript library support, enhanced SQL stored routine APIs, ENUM and SET handling, EXPLAIN FORMAT=JSON version 2, while deprecating several version‑token functions and privileges and removing keyword restrictions, providing a comprehensive overview of additions, deprecations, and removals.

JavaScriptMySQLNewFeatures
0 likes · 9 min read
New and Changed Features in MySQL 9.2.0
Architect's Guide
Architect's Guide
Jan 21, 2025 · Databases

Why Store IPv4 Addresses as UNSIGNED INT in MySQL: Benefits, Drawbacks, and Conversion Techniques

The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves index and range‑query performance, outlines the storage savings compared to VARCHAR, mentions the need for manual conversion, and provides MySQL and Java code examples for converting between string and integer representations.

Data StorageIPv4MySQL
0 likes · 5 min read
Why Store IPv4 Addresses as UNSIGNED INT in MySQL: Benefits, Drawbacks, and Conversion Techniques
Programmer1970
Programmer1970
Jan 19, 2025 · Databases

MySQL Interview Scenario: Analyzing E‑Commerce User Repurchase Behavior

This article presents a MySQL interview case that examines e‑commerce repurchase behavior using orders and users tables, defines repeat purchasers, computes average repurchase intervals and amounts, identifies high‑value customers, and aggregates results by city through CTEs, window functions, and grouped statistics.

CTECity-Level MetricsE-commerce Analytics
0 likes · 8 min read
MySQL Interview Scenario: Analyzing E‑Commerce User Repurchase Behavior
Java Tech Enthusiast
Java Tech Enthusiast
Jan 19, 2025 · Databases

A Low‑Level Bug Caused by Decimal Precision Mistake: Lessons on Code Review, Testing, and Process

A simple typo that changed a daily interest rate from 0.00006944444 to 0.00069444444 inflated calculated interest tenfold, went unnoticed because the author self‑approved the SQL update and testing only checked syntax, and the incident underscores the need for rigorous code review, testing, and change‑management procedures for critical data.

BackendCode ReviewSQL
0 likes · 12 min read
A Low‑Level Bug Caused by Decimal Precision Mistake: Lessons on Code Review, Testing, and Process
Java Web Project
Java Web Project
Jan 15, 2025 · Backend Development

Why MyBatis foreach Batch Inserts Stall and How to Speed Them Up with ExecutorType.BATCH

The article investigates a MyBatis batch‑insert job that consumes excessive CPU and takes 14 minutes, explains why the foreach‑generated giant INSERT statement is inefficient, and demonstrates how switching to ExecutorType.BATCH or MyBatis dynamic‑SQL batch support reduces the runtime to under two seconds.

Batch InsertExecutorType.BATCHJava
0 likes · 10 min read
Why MyBatis foreach Batch Inserts Stall and How to Speed Them Up with ExecutorType.BATCH
Java Tech Enthusiast
Java Tech Enthusiast
Jan 15, 2025 · Databases

How SQL Statements Are Executed in a Database

When a client sends an SQL statement over TCP, the parser checks syntax and semantics and builds a query tree, the optimizer evaluates possible execution plans using statistics to estimate costs and selects the most efficient one, and the executor runs the plan, using the buffer manager for reads or the transaction manager for updates while the lock manager enforces ACID properties.

OptimizationSQLdatabase
0 likes · 3 min read
How SQL Statements Are Executed in a Database
Programmer1970
Programmer1970
Jan 14, 2025 · Databases

MySQL Interview Scenario: Advanced SQL Analytics with Joins, Subqueries, and Window Functions

This article presents an e‑commerce interview case that defines three tables (orders, order_items, products), loads sample data, and walks through a step‑by‑step SQL solution using CTEs, aggregation, window functions and HAVING to calculate each customer's total spend, most expensive order, and favorite product category, filtering and sorting the results.

AggregationCTEMySQL
0 likes · 9 min read
MySQL Interview Scenario: Advanced SQL Analytics with Joins, Subqueries, and Window Functions
Alibaba Cloud Developer
Alibaba Cloud Developer
Jan 14, 2025 · Databases

Mastering MySQL Indexes: From Hash Tables to B+ Trees and Optimization Tips

This article explains the concept of database indexes, compares various index structures such as hash tables, ordered arrays, binary trees, B‑trees and B+‑trees, details InnoDB primary, secondary, covering and composite indexes, discusses prefix indexes and order‑by optimization, and provides practical advice and SQL examples for improving MySQL query performance.

B+ TreeMySQLSQL
0 likes · 18 min read
Mastering MySQL Indexes: From Hash Tables to B+ Trees and Optimization Tips
Top Architect
Top Architect
Jan 12, 2025 · Backend Development

Optimizing a High‑Concurrency Interface: Reducing Response Time from 30 s to 0.8 s

This article presents a real‑world case study of a high‑concurrency data‑processing interface whose response time was reduced from 30 seconds to under one second by diagnosing SQL bottlenecks, applying PostgreSQL array aggregation, moving logic to the database, and introducing Caffeine caching, with detailed code examples and performance analysis.

BackendCachingMyBatis
0 likes · 13 min read
Optimizing a High‑Concurrency Interface: Reducing Response Time from 30 s to 0.8 s
Java Tech Enthusiast
Java Tech Enthusiast
Jan 8, 2025 · Databases

Recovering Accidentally Deleted MySQL Data Using Binlog

To recover accidentally deleted MySQL rows, ensure binary logging is enabled and set to ROW mode, locate the appropriate binlog files, use mysqlbinlog (or tools like binlog2sql) to extract the original INSERT events within a time window, and replay or reconstruct them to restore the data.

BinlogMySQLSQL
0 likes · 16 min read
Recovering Accidentally Deleted MySQL Data Using Binlog
ITPUB
ITPUB
Jan 7, 2025 · Databases

Cut Costs 25% and Boost Performance 70%: Retail Giant’s OceanBase Migration

The article details how WanJia Shuke, the tech arm of China Resources Vanguard, tackled retail system fragmentation, user‑experience degradation, complex linkages and scalability limits by migrating dozens of projects to the distributed OceanBase database, achieving up to 70% performance improvement, 25% cost reduction and streamlined operations.

FlinkOceanBaseSQL
0 likes · 15 min read
Cut Costs 25% and Boost Performance 70%: Retail Giant’s OceanBase Migration
Top Architecture Tech Stack
Top Architecture Tech Stack
Jan 6, 2025 · Backend Development

10 Essential MyBatis Development Tips for Efficient Backend Programming

This article presents ten practical MyBatis techniques—including dynamic SQL, resultMap, foreach, pagination, annotation proxies, second‑level cache, dynamic table names, custom type handlers, logging, and multi‑datasource configuration—to help Java developers write cleaner, safer, and more performant data‑access code.

JavaMyBatisORM
0 likes · 11 min read
10 Essential MyBatis Development Tips for Efficient Backend Programming
Big Data Technology & Architecture
Big Data Technology & Architecture
Dec 31, 2024 · Big Data

Eliminating Shuffle in Spark Joins with Storage Partitioned Join (SPJ) for Iceberg Tables

This article explains how Spark ≥ 3.3 introduces Storage Partitioned Join (SPJ) to avoid costly shuffle operations when joining partitioned V2 source tables such as Apache Iceberg, detailing the required conditions, configuration settings, practical code examples, and various join scenarios including mismatched partitions and data skew.

BucketingData SkewSQL
0 likes · 15 min read
Eliminating Shuffle in Spark Joins with Storage Partitioned Join (SPJ) for Iceberg Tables
JD Tech
JD Tech
Dec 30, 2024 · Big Data

Techniques for Writing Elegant and Efficient SQL in Big Data Environments

The article shares practical methods and code examples for making SQL both readable and high‑performing in large‑scale data platforms, covering predicate push‑down with subqueries, deduplication strategies, bucket utilization, and Python‑driven job parameter handling.

HiveSQLSpark
0 likes · 14 min read
Techniques for Writing Elegant and Efficient SQL in Big Data Environments
ITPUB
ITPUB
Dec 26, 2024 · Databases

Why SELECT * Can Kill Your API Performance: Lessons from a 2012 Database Mishap

This article recounts a 2012 incident where a backend API slowed dramatically after hidden BLOB columns were added, and explains why using SELECT *—which blocks index‑only scans, forces extra I/O, increases deserialization, network, and client processing costs, and hampers schema maintenance—should be avoided in favor of explicit column lists.

PostgreSQLQuery OptimizationSQL
0 likes · 7 min read
Why SELECT * Can Kill Your API Performance: Lessons from a 2012 Database Mishap
macrozheng
macrozheng
Dec 26, 2024 · Databases

Master DBeaver: Free Open‑Source Tool for Managing 100+ Databases

This guide introduces the open‑source DBeaver database manager, walks through downloading, installing, and configuring it, demonstrates its use with a SpringBoot3 + Vue e‑commerce project, and covers theme settings, data source creation, table operations, SQL editing, and its support for various database types.

DBeaverDatabase ManagementSQL
0 likes · 7 min read
Master DBeaver: Free Open‑Source Tool for Managing 100+ Databases
DataFunTalk
DataFunTalk
Dec 25, 2024 · Databases

Applying RisingWave to Real-Time Feature Engineering: Architecture, Capabilities, and Use Cases

This article introduces RisingWave, an open‑source streaming database, and explains how its SQL‑based interface, compute‑storage separation, UDF support, and materialized views enable efficient real‑time feature engineering, state management, and diverse downstream applications, including the enhancements in RisingWave 2.0.

Materialized ViewsReal-time Feature EngineeringRisingWave
0 likes · 17 min read
Applying RisingWave to Real-Time Feature Engineering: Architecture, Capabilities, and Use Cases
Su San Talks Tech
Su San Talks Tech
Dec 24, 2024 · Backend Development

10 Must‑Know MyBatis Tricks to Supercharge Your Java Backend

Discover ten powerful MyBatis techniques—from dynamic SQL and resultMap customization to batch operations, pagination, annotation‑based mappers, caching, dynamic table names, custom type handlers, logging, and multi‑datasource configuration—that can dramatically improve Java backend development efficiency and code quality.

JavaORMSQL
0 likes · 10 min read
10 Must‑Know MyBatis Tricks to Supercharge Your Java Backend
Alibaba Cloud Native
Alibaba Cloud Native
Dec 19, 2024 · Big Data

Boosting SLS SQL: 3× Faster Queries on Trillion‑Row Logs

Alibaba Cloud’s Serverless Log Service (SLS) has overhauled its SQL engine with a C++‑based compute engine, SIMD acceleration, storage‑compute fusion, and optimized scheduling, delivering up to three‑fold speed gains, 50% latency reduction, and significant improvements across high‑cardinality, JSON, IP, and join queries.

Log AnalyticsSQLbig data
0 likes · 12 min read
Boosting SLS SQL: 3× Faster Queries on Trillion‑Row Logs
Alibaba Cloud Big Data AI Platform
Alibaba Cloud Big Data AI Platform
Dec 19, 2024 · Big Data

MaxCompute Bloomfilter Index: Faster Emergency Tracing Queries, Reduced Storage

The article explains how MaxCompute’s newly introduced Bloomfilter index dramatically improves emergency data tracing by cutting query time and resource consumption, replacing costly secondary indexes, reducing storage by over 45%, and providing a lightweight, high‑efficiency solution for large‑scale point‑lookup scenarios.

BloomFilterMaxComputeSQL
0 likes · 12 min read
MaxCompute Bloomfilter Index: Faster Emergency Tracing Queries, Reduced Storage
Java Tech Enthusiast
Java Tech Enthusiast
Dec 13, 2024 · Databases

Impact of VARCHAR Length on MySQL Storage and Query Performance

Testing shows that VARCHAR(50) and VARCHAR(500) occupy identical storage, yet while simple lookups perform similarly, sorting on the longer column triggers disk‑based mergesort and can be several times slower, demonstrating that excessive VARCHAR length harms query performance without saving space.

MySQLSQLStorage
0 likes · 10 min read
Impact of VARCHAR Length on MySQL Storage and Query Performance
Aikesheng Open Source Community
Aikesheng Open Source Community
Dec 12, 2024 · Databases

Using EXPLAIN to Analyze and Optimize a Simple MySQL Query

This article demonstrates how to use MySQL's EXPLAIN statement to examine execution plans, interpret key fields such as possible_keys, key, rows, and Extra, and apply index adjustments—including adding and forcing indexes—to improve query performance while weighing sorting costs.

EXPLAINIndex OptimizationMySQL
0 likes · 11 min read
Using EXPLAIN to Analyze and Optimize a Simple MySQL Query
Su San Talks Tech
Su San Talks Tech
Dec 10, 2024 · Databases

Master MySQL: 13 Essential Functions & Commands Every Developer Should Know

This article walks through 13 practical MySQL techniques—including group_concat, char_length, locate, replace, now, various INSERT variations, SELECT FOR UPDATE, on duplicate key update, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST, and mysqldump—providing clear examples, SQL snippets, and screenshots to help developers write more efficient and reliable queries.

FunctionsMySQLQueries
0 likes · 14 min read
Master MySQL: 13 Essential Functions & Commands Every Developer Should Know
ITPUB
ITPUB
Dec 8, 2024 · Databases

How to Refactor Complex SQL Like Java Code: A Step‑by‑Step Case Study

This article walks through a real‑world slow‑SQL case from a logistics routing system, demonstrating how systematic formatting, layer decomposition, condition push‑down, join optimization, and validation can transform a tangled query into a clean, performant, and maintainable statement.

SQLrefactoring
0 likes · 15 min read
How to Refactor Complex SQL Like Java Code: A Step‑by‑Step Case Study
Rare Earth Juejin Tech Community
Rare Earth Juejin Tech Community
Dec 7, 2024 · Backend Development

Online SQL-to-ER Diagram Tool: Features, Usage, and Implementation Details

This article introduces an online SQL-to-ER diagram tool, explains its advantages, provides step‑by‑step usage instructions, showcases example SQL and Java code for parsing and generating ER diagrams, and discusses the underlying design‑pattern architecture such as Strategy, Factory, Dependency Injection, and Singleton.

Design PatternsER diagramJava
0 likes · 17 min read
Online SQL-to-ER Diagram Tool: Features, Usage, and Implementation Details
macrozheng
macrozheng
Dec 4, 2024 · Databases

Why MySQL Pagination Slows Down at Scale and How to Speed It Up

This article examines how pagination queries on large MySQL tables become dramatically slower as the offset grows, presents real‑world performance measurements, and offers three practical optimization strategies—including selecting only primary keys, range‑based filtering, and using ElasticSearch—to keep query latency low.

BackendSQLdatabase optimization
0 likes · 10 min read
Why MySQL Pagination Slows Down at Scale and How to Speed It Up
MaGe Linux Operations
MaGe Linux Operations
Dec 3, 2024 · Databases

Master MySQL Backups: Commands, Scripts, and Best Practices

This guide explains how to back up single or multiple MySQL databases, entire server instances, specific tables, or only schema, using mysqldump with options like -B, gzip compression, add‑drop flags, and provides restore procedures and an automated backup script.

GZIPMySQLRestore
0 likes · 10 min read
Master MySQL Backups: Commands, Scripts, and Best Practices
Selected Java Interview Questions
Selected Java Interview Questions
Nov 29, 2024 · Databases

12 MyBatis‑Plus Optimization Tips for Efficient Database Operations

This article presents twelve practical MyBatis‑Plus optimization techniques—including avoiding isNull checks, specifying select fields, batch operations, using EXISTS, safe ordering, type‑safe Lambda queries, handling nulls, performance tracking, enum mapping, logical deletion, optimistic locking, and increment/decrement methods—to improve code readability, maintainability, and database query performance.

JavaMybatisPlusORM
0 likes · 14 min read
12 MyBatis‑Plus Optimization Tips for Efficient Database Operations
IT Services Circle
IT Services Circle
Nov 27, 2024 · Databases

15 Common MySQL Pitfalls and How to Avoid Them

This article outlines fifteen typical MySQL pitfalls—including missing WHERE clauses, lack of indexes, improper NULL handling, wrong data types, deep pagination, missing EXPLAIN analysis, charset misconfiguration, SQL injection risks, transaction misuse, collation issues, overusing SELECT *, index loss, frequent schema changes, missing backups, and unarchived historical data—and provides concrete examples and best‑practice solutions to improve performance, reliability, and security.

MySQLSQLTransactions
0 likes · 13 min read
15 Common MySQL Pitfalls and How to Avoid Them
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Nov 27, 2024 · Databases

Understanding MySQL Database Transactions: ACID Properties, Control Statements, and Isolation Levels

This article explains the concept and importance of MySQL database transactions, describes the ACID properties, shows how to use transaction control statements and savepoints, and demonstrates each isolation level (read uncommitted, read committed, repeatable read, serializable) with practical SQL examples.

ACIDDatabase TransactionsMySQL
0 likes · 10 min read
Understanding MySQL Database Transactions: ACID Properties, Control Statements, and Isolation Levels
Su San Talks Tech
Su San Talks Tech
Nov 26, 2024 · Databases

15 Common MySQL Pitfalls and How to Avoid Them

This article outlines fifteen frequent MySQL mistakes—from missing WHERE clauses and absent indexes to improper data types, deep pagination, SQL injection risks, and inadequate backups—providing clear examples and practical solutions to improve query performance, data integrity, and overall database reliability.

MySQLSQLdatabase optimization
0 likes · 15 min read
15 Common MySQL Pitfalls and How to Avoid Them
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Nov 21, 2024 · Databases

Common MySQL Data Types and Selection Guidelines

This article explains MySQL's various numeric, date/time, and string data types, discusses their characteristics such as signed/unsigned integers, floating‑point precision, fixed‑point accuracy, and provides practical best‑practice recommendations for choosing optimal types in database design.

Data TypesMySQLSQL
0 likes · 6 min read
Common MySQL Data Types and Selection Guidelines
Su San Talks Tech
Su San Talks Tech
Nov 16, 2024 · Databases

Why MySQL Pagination Slows Down on Large Tables and How to Fix It

This article examines how pagination queries on massive MySQL tables become dramatically slower as the offset grows, defines what constitutes a slow SQL, and presents three practical optimization techniques—including returning only primary keys, range filtering, and using Elasticsearch—to dramatically improve query performance.

ElasticsearchMySQLSQL
0 likes · 10 min read
Why MySQL Pagination Slows Down on Large Tables and How to Fix It
Open Source Tech Hub
Open Source Tech Hub
Nov 16, 2024 · Databases

Build Real‑Time Analytics with StarRocks: Quickstart Tutorial and Sample Queries

This guide introduces StarRocks, a high‑performance MPP database, explains its architecture and typical use cases, walks through a Docker‑based quickstart, shows how to create databases and tables, load NYC crash and weather datasets via Stream Load, and demonstrates analytical SQL queries that reveal traffic‑accident patterns under different weather conditions.

DockerMPP databaseReal-time Analytics
0 likes · 18 min read
Build Real‑Time Analytics with StarRocks: Quickstart Tutorial and Sample Queries
ITPUB
ITPUB
Nov 15, 2024 · Databases

Why Vector Databases Matter: Deploying PgVector on PostgreSQL for Scalable AI Retrieval

This article explains the need for vector databases in the AI era, reviews PostgreSQL's extensible ecosystem, compares vector‑database options, provides step‑by‑step PgVector installation and usage, shares operational best practices, performance tuning tips, and real‑world Qunar & Tujia case studies.

AIPgVectorPostgreSQL
0 likes · 27 min read
Why Vector Databases Matter: Deploying PgVector on PostgreSQL for Scalable AI Retrieval
Liangxu Linux
Liangxu Linux
Nov 14, 2024 · Databases

Master SQLite with C: Essential Functions and Sample Code

This guide walks through using SQLite's C API—including opening databases, executing queries with and without callbacks, handling results, and performing insert, delete, and display operations—complete with code snippets, table schemas, and compilation instructions.

APIC++SQL
0 likes · 12 min read
Master SQLite with C: Essential Functions and Sample Code
dbaplus Community
dbaplus Community
Nov 12, 2024 · Databases

How to Query Elasticsearch with SQL: A Step‑by‑Step Guide

This tutorial shows how to use Elasticsearch's built‑in SQL support to run familiar MySQL‑style queries—creating an index, inserting documents, and performing SELECT, WHERE, ORDER BY, GROUP BY, LIMIT, aggregation, date and string functions—making ES data retrieval as easy as relational databases.

Data RetrievalElasticsearchSQL
0 likes · 7 min read
How to Query Elasticsearch with SQL: A Step‑by‑Step Guide
Java Tech Enthusiast
Java Tech Enthusiast
Nov 12, 2024 · Databases

The Origin Story of Covering Indexes in SQLite

The article recounts how SQLite’s creator, D. Richard Hipp, learned about covering indexes from MySQL co‑founder David Axmark at a German PHP conference and, during a trans‑Atlantic Delta flight, added support that lets queries be satisfied entirely from the index, dramatically boosting performance by avoiding table reads.

Covering IndexSQLSQLite
0 likes · 5 min read
The Origin Story of Covering Indexes in SQLite
ITPUB
ITPUB
Nov 11, 2024 · Databases

When Does MySQL Actually Update Index Blocks? A Deep Dive into InnoDB Update Mechanics

This article examines how MySQL InnoDB decides whether to modify index blocks during UPDATE statements, walks through the internal mysql_update workflow, shows debugging with GDB, explains three test scenarios, and validates the behavior by inspecting block LSNs with the innblock tool.

Database InternalsIndex UpdateInnoDB
0 likes · 18 min read
When Does MySQL Actually Update Index Blocks? A Deep Dive into InnoDB Update Mechanics
YiSu Grain
YiSu Grain
Nov 7, 2024 · Operations

Building a Safety Equipment Warning System with FineReport – Step‑by‑Step Guide

This article walks through creating a safety‑protective‑equipment warning management system using FineReport, covering MySQL table setup, data import templates, batch handling, validation formulas, SQL‑driven datasets, pagination controls, batch deletion, warning calculations, detailed view reports, and dashboard visualisation with pie, bar and gauge charts.

BIDataValidationFineReport
0 likes · 23 min read
Building a Safety Equipment Warning System with FineReport – Step‑by‑Step Guide
Architect
Architect
Nov 6, 2024 · Databases

Storing IPv4 as Unsigned Int in MySQL: Benefits, Drawbacks & Code

Using an unsigned INT to store IPv4 addresses in MySQL saves space and enables efficient range queries, while strings are larger and slower; the article explains these advantages, outlines conversion functions INET_ATON/INET_NTOA, shows equivalent handling for IPv6, and provides Java utilities for bidirectional conversion.

Data StorageIPv4Java
0 likes · 6 min read
Storing IPv4 as Unsigned Int in MySQL: Benefits, Drawbacks & Code
Architect's Tech Stack
Architect's Tech Stack
Nov 6, 2024 · Backend Development

Optimizing MyBatis Batch Insert Performance with ExecutorType.BATCH and Proper Value Chunking

This article explains why using MyBatis foreach for bulk inserts can cause severe performance degradation, analyzes the underlying cost of large prepared statements, and demonstrates how switching to ExecutorType.BATCH or limiting each INSERT to 20‑50 rows dramatically improves insertion speed.

Batch InsertExecutorType.BATCHJava
0 likes · 8 min read
Optimizing MyBatis Batch Insert Performance with ExecutorType.BATCH and Proper Value Chunking
Architect
Architect
Nov 4, 2024 · Databases

SQL Refactoring Case Study: Optimizing Complex Queries in the JDL Routing System

This article examines how uncontrolled growth of SQL code leads to performance and maintainability issues, then demonstrates a step‑by‑step refactoring of a complex routing‑system query—formatting, layer decomposition, merging, condition push‑down, join optimization, and testing—to achieve clearer, faster, and more reliable database operations.

Query TuningSQLdatabase optimization
0 likes · 14 min read
SQL Refactoring Case Study: Optimizing Complex Queries in the JDL Routing System
Big Data Technology & Architecture
Big Data Technology & Architecture
Nov 4, 2024 · Databases

Detailed Analysis of Doris SQL Execution Process: Optimizer, Scheduler, and Executor

This article provides a comprehensive walkthrough of Doris's SQL execution pipeline, covering the query optimizer's parsing, rewriting, and plan generation, the scheduler's fragment distribution, and the executor's fragment processing, including code examples of expression rewrite rules, join strategies, and data flow between FE and BE nodes.

Distributed ExecutionDorisSQL
0 likes · 30 min read
Detailed Analysis of Doris SQL Execution Process: Optimizer, Scheduler, and Executor
Architecture Digest
Architecture Digest
Nov 3, 2024 · Backend Development

Using Easy-Query ORM for Strongly Typed OLTP and OLAP Queries in Java

This article introduces Easy-Query, a Java ORM that offers strong‑typed OLTP and OLAP query capabilities, demonstrates how to define entity classes with many‑to‑many and one‑to‑one relationships, and provides multiple code examples for complex queries, DTO generation, and automatic inclusion of related data.

Easy-QueryJavaOLAP
0 likes · 11 min read
Using Easy-Query ORM for Strongly Typed OLTP and OLAP Queries in Java
Java Tech Enthusiast
Java Tech Enthusiast
Nov 1, 2024 · Databases

Quick MySQL Configuration and Monitoring Queries

This guide presents essential MySQL configuration and monitoring queries—covering connection limits, Binlog/GTID status, InnoDB settings—plus a one‑click script that consolidates these checks, enabling quick health assessments and more efficient routine inspections of MySQL servers.

MySQLSQLdatabase
0 likes · 2 min read
Quick MySQL Configuration and Monitoring Queries
Programmer XiaoFu
Programmer XiaoFu
Oct 30, 2024 · Databases

How to Boost Pagination Queries for a Million Products by 10×

This article walks through nine practical techniques—default filters, smaller page sizes, fewer joins, index tuning, straight_join, data archiving, efficient count(*), ClickHouse offloading, and read/write splitting—to dramatically improve the performance of pagination APIs handling millions of product records.

ClickHouseIndex OptimizationMySQL
0 likes · 11 min read
How to Boost Pagination Queries for a Million Products by 10×
JavaEdge
JavaEdge
Oct 28, 2024 · Databases

Why Do MySQL Transactions Deadlock? Reproduce and Prevent InnoDB Deadlocks

This article explains how MySQL InnoDB deadlocks occur during order‑record idempotency checks, demonstrates step‑by‑step reproduction with SQL scripts, analyzes the lock types involved, and provides practical strategies to avoid and resolve such deadlocks in production systems.

InnoDBMySQLSQL
0 likes · 13 min read
Why Do MySQL Transactions Deadlock? Reproduce and Prevent InnoDB Deadlocks
Su San Talks Tech
Su San Talks Tech
Oct 26, 2024 · Databases

9 Proven Tricks to Supercharge Your Pagination API Performance

This article presents nine practical techniques—including default filters, smaller page sizes, reduced joins, index tuning, straight_join usage, data archiving, count(*) optimization, ClickHouse offloading, and read/write splitting—to dramatically improve the speed and scalability of pagination query interfaces.

BackendSQLpagination
0 likes · 13 min read
9 Proven Tricks to Supercharge Your Pagination API Performance
DaTaobao Tech
DaTaobao Tech
Oct 25, 2024 · Big Data

Using Temporary Table JOIN in Flink SQL for Real-Time Stream Enrichment

The article explains how to use Flink SQL’s temporary table join to enrich a real‑time traffic‑log stream with versioned tag data, detailing the required DDL, the time‑versioned join syntax, and essential watermark and idle‑timeout settings that prevent stalls and boundary‑delay issues.

FlinkSQLTemporary Join
0 likes · 7 min read
Using Temporary Table JOIN in Flink SQL for Real-Time Stream Enrichment
IT Services Circle
IT Services Circle
Oct 25, 2024 · Databases

Database Management Challenges in the Cloud Era and How Apache ShardingSphere Addresses Them

The article outlines the growing difficulties of managing diverse databases in cloud-native environments, introduces Apache ShardingSphere as a comprehensive open‑source solution with three core capabilities—connectivity, enhancement, and pluggability—and guides readers through a three‑step learning path from fundamentals to deployment and testing.

Database ManagementDatabase MiddlewareSQL
0 likes · 9 min read
Database Management Challenges in the Cloud Era and How Apache ShardingSphere Addresses Them
php Courses
php Courses
Oct 22, 2024 · Backend Development

Using PHP mysqli_query to Execute MySQL Queries

This article explains how to use PHP's mysqli_query function to connect to a MySQL database, execute SELECT queries, handle result sets, and perform other operations such as INSERT, UPDATE, and DELETE, including a complete example code snippet and best practices for error handling.

MySQLPHPSQL
0 likes · 5 min read
Using PHP mysqli_query to Execute MySQL Queries
Rare Earth Juejin Tech Community
Rare Earth Juejin Tech Community
Oct 19, 2024 · Databases

Understanding the HAVING Clause in SQL: Concepts, Examples, and Best Practices

This article explains the purpose and proper use of the SQL HAVING clause, contrasts it with WHERE, and provides multiple practical examples—including counting groups, detecting missing IDs, calculating mode and median, and filtering fully‑submitted records—while highlighting common pitfalls and performance considerations.

AggregationGROUP BYHAVING
0 likes · 13 min read
Understanding the HAVING Clause in SQL: Concepts, Examples, and Best Practices
dbaplus Community
dbaplus Community
Oct 17, 2024 · Databases

What’s New in MySQL 9.1.0? A Deep Dive into Latest Features and Fixes

MySQL Innovation Edition 9.1.0, released on October 15 2024, introduces atomic DDL operations, enhanced audit and firewall handling, numerous compiler and SQL function fixes, JavaScript stored‑procedure improvements, new vector support, updated keyring security, pluggable authentication fixes, expanded GROUP REPLICATION logging, and several EXPLAIN and performance‑schema enhancements.

Database FeaturesGroup ReplicationJavaScript stored procedures
0 likes · 10 min read
What’s New in MySQL 9.1.0? A Deep Dive into Latest Features and Fixes
360 Tech Engineering
360 Tech Engineering
Oct 17, 2024 · Databases

Introducing DataFusion: A High‑Performance Rust‑Based Query Engine Powered by Apache Arrow

This article explains DataFusion, a Rust‑written, Arrow‑based query engine that offers high performance, extensibility, and seamless integration with various data sources, detailing its architecture, execution model, Rust advantages, and practical usage examples for building modern data‑warehouse solutions.

Apache ArrowDataFusionQuery Engine
0 likes · 15 min read
Introducing DataFusion: A High‑Performance Rust‑Based Query Engine Powered by Apache Arrow
dbaplus Community
dbaplus Community
Oct 15, 2024 · Databases

How to Safely Perform Full-Table Updates on Billion-Row MySQL Tables

Updating billions of rows in a MySQL table can overwhelm binlog replication and cause deep‑pagination inefficiencies, so this article explains the pitfalls of direct UPDATE, explores limit‑based and IN‑based approaches, and presents a production‑ready batch update strategy using NO_CACHE and forced primary‑key indexing.

Batch ProcessingBinlogFull Table Update
0 likes · 7 min read
How to Safely Perform Full-Table Updates on Billion-Row MySQL Tables
ITPUB
ITPUB
Oct 14, 2024 · Databases

How Long Can MySQL AUTO_INCREMENT IDs Last? INT vs BIGINT Explained

This article examines MySQL's AUTO_INCREMENT integer types, comparing the 4‑byte INT and 8‑byte BIGINT limits, calculating how long each can store records under realistic insertion rates, and providing step‑by‑step SQL commands and safety tips for converting INT columns to BIGINT.

AUTO_INCREMENTData TypesMySQL
0 likes · 6 min read
How Long Can MySQL AUTO_INCREMENT IDs Last? INT vs BIGINT Explained
dbaplus Community
dbaplus Community
Oct 13, 2024 · Databases

Master MySQL Data Masking: Percona Plugin & Custom Functions Guide

Learn how to protect sensitive information in MySQL by installing the open‑source Percona data_masking plugin, verifying its status, and using built‑in and custom masking functions for IDs, phone numbers, emails, names, amounts, and addresses, with step‑by‑step SQL examples.

MySQLPerconaSQL
0 likes · 7 min read
Master MySQL Data Masking: Percona Plugin & Custom Functions Guide
Programmer1970
Programmer1970
Oct 12, 2024 · Databases

MySQL Index Interview Cheat Sheet: Curated Questions with In‑Depth Answers

This article compiles a comprehensive set of MySQL index interview questions covering index types, usage scenarios, the left‑most prefix rule, optimization techniques, index fragmentation, InnoDB clustered vs. non‑clustered indexes, composite and spatial indexes, full‑text indexes, the concept of "back‑table" lookups, execution plans, EXPLAIN output columns, and practical tips for interpreting and improving query performance.

EXPLAINMySQLSQL
0 likes · 36 min read
MySQL Index Interview Cheat Sheet: Curated Questions with In‑Depth Answers
Java Architect Essentials
Java Architect Essentials
Oct 11, 2024 · Backend Development

Mastering EasyQuery: A Strongly Typed Java ORM with Real‑World Query Examples

This article explains why the author created the EasyQuery ORM for Java, demonstrates a wide range of query patterns—including single‑record, pagination, joins, subqueries, streaming, custom VO mapping, dynamic conditions, grouping, native SQL, function columns, and high‑performance encryption—while providing complete code snippets and links to documentation and source repositories.

BackendJavaORM
0 likes · 13 min read
Mastering EasyQuery: A Strongly Typed Java ORM with Real‑World Query Examples