Copyright © 2023 PawSQL

Problem Definition​

Using the LIMIT clause in UPDATE/DELETE operations can lead to master-slave data inconsistency or interruption of slave synchronization.

PawSQL Auditing​

PawSQL provides a corresponding SQL Audit warning for this case.

We often see articles warning that using SELECT * is a bad habit and columns should be explicitly specified in queries, such as in the latest Alibaba Java Coding Guidelines (Taishan Edition) where it is a mandatory rule. However, people only have a vague understanding of the reasons behind avoiding SELECT *. This article explains 9 reasons to avoid using SELECT * from two perspectives, and also discusses two reasonable exceptional cases to use SELECT *.

Copyright © 2023 PawSQL

Problem Definition​

STRAIGHT_JOIN is a table join method in MySQL that forces joining tables in the order defined, equivalent to an inner join in result. It gives developers some control over how the database executes the SQL query. However, it also loses out on join order optimizations by the optimizer, so should be used carefully based on the scenario. PawSQL provides a risk warning for statements using STRAIGHT_JOIN to avoid potential performance issues caused by it.

Taking the lineitem and orders tables in the tpch database as an example, the following query will join the lineitem table first and orders table second directly, indicating the database should take lineitem as the driving table and orders as the driven table for the join operation, without optimizing the table order.

SELECT *
FROM lineitem 
STRAIGHT_JOIN orders ON lineitem.l_orderkey = orders.o_orderkey;

PostgreSQL​

数据访问​

SEQ SCAN: 通过顺序扫描输入记录集来查找相关记录，顺序扫描（与索引扫描不同）执行单个读取操作。 CTE SCAN: 对公共表达式（CTE）查询结果执行顺序扫描。请注意，CTE的结果被计算并临时存储(Materialization)。 INDEX SCAN: 根据索引查找相关记录。索引扫描执行两次读取操作：一次读取索引，另一次从表中读取实际值。

Channel of advanced SQL tuning

Overview​

NULL value processing is the most error-prone for database application developers, mainly because we are accustomed to using binary Boolean logic, while the database's processing logic for NULL values is three-valued logic. In fact, the most flawed component in the database optimizers is the logic related to NULL value processing. Even mature database software, such as DB2/Teradata, still has more than 20% of the bugs related NULL processing.

In this article, we analyze the root causes of the NULL value pitfalls, and conclude with a simple and effective examination logic to infer the final result. At the same time, we explain the applicable conditions and solutions for the four common scenarios in daily development work. After reading this article, you will be able to cope with all the scenarios regarding NULL value handling in your daily SQL processing work.