27 posts tagged with "pawsql"

Problem​

Functionrand(MySQL) or random(PostgreSQL) returns a random floating-point value in the range 0 to 1.0. It is useful to get a random sample of dataset using following query.

select * from orders order by rand() limit 1; 

This works fine if customer table is less than 10,000 rows. But when you have 1,000,000 rows, the overhead for sorting the rows becomes unacceptable. The reason is quiet clear: we sort all rows, but only keep one.

Indexes are one of the most important tools for optimizing database performance. However, creating too many indexes or indexing the wrong columns can also have a negative impact on performance. Therefore, it is important to follow certain principles when designing indexes.

A database index is a data structure used to speed up database queries. It is similar to the catalog of books and can help quickly locate a certain row or rows in a table. A database index usually consists of a set of index keys (or index fields) whose values are stored in a data structure for fast lookup of specific rows.

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 *.