SQL Optimization Tips Summary

The summary is as follows:

Decompose SQL

The most typical case of query segmentation is paging query

Always set an ID primary key for each table

Avoid using SELECT *

Index the search field

Use the column of the corresponding type when joining the table and index it

Use NOT NULL whenever possible

Smaller columns are faster

Use LIMIT 1 when only one row of data is required

Operator optimization, try not to use operators that are not conducive to indexes, the purpose is to avoid full table scans

1. Use in and not in with caution, try to use between instead of in, and use not exists instead of not in

2. Use is null and is not null with caution

3. The != or <> operator can be used without it, otherwise the engine will give up the use of the index and perform a full table scan.