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