How to improve SQL execution efficiency?

1. Avoid full table scans as much as possible, and first consider creating indexes on columns involved in the WHERE and ORDER BY clauses.
2. Avoid performing NULL value judgments on fields in the WHERE clause, as this may cause the engine to abandon the use of indexes and perform a full table scan. For example: select id from t where num is null Instead, set a default value of 0 for num to ensure there are no NULL values in the num column, and then query like this: select id from t where num=0
3. Avoid using != or <> operators in the WHERE clause, as this may cause the engine to abandon the use of indexes and perform a full table scan.
4. Avoid using OR to connect conditions in the WHERE clause, as this may lead the engine to abandon the use of indexes and perform a full table scan. For example: select id from t where num=10 or num=20 Instead, query like this: select id from t where num=10 union all select id from t where num=20
5. Be cautious when using IN and NOT IN, as they can also lead to full table scans. For example: select id from t where num in(1,2,3) For consecutive numerical values, use BETWEEN instead of IN when possible: select id from t where num between 1 and 3
6. The following query will also result in a full table scan: select id from t where name like '%abc%'
7. Avoid performing expression operations on fields in the WHERE clause, as this may cause the engine to abandon the use of indexes and perform a full table scan. For example: select id from t where num/2=100 Instead, query like this: select id from t where num=100*2
8. Avoid performing function operations on fields in the WHERE clause, as this may cause the engine to abandon the use of indexes and perform a full table scan. For example: select id from t where substring(name,1,3)='abc' -- ids where name starts with 'abc' Instead, query like this: select id from t where name like 'abc%'
9. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, as the system may not be able to use indexes correctly.
10. When using indexed fields as conditions, if the index is a composite index, the first field in the index must be used as a condition to ensure that the system uses the index. Otherwise, the index will not be used, and the field order should be as consistent as possible with the index order. ** https://www.sqlynx.com/**