1. To optimize the query, avoid full-table scanning as far as possible, and first consider establishing an index on the columns involved in the Where and order by.
2. Avoid null-valued fields in the WHERE clause, which will cause the engine to discard full-table scans using the index, such as:
Select ID from t where num is null
You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:
Select ID from t where num=0
3. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.
4. You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for a full table scan, such as:
Select ID from t where num=10 or num=20
You can query this:
Select ID from t where num=10
UNION ALL
Select ID from t where num=20
5.in and not in should also be used with caution, otherwise it will result in full table scans, such as:
Select ID from t where num in
For consecutive values, you can use between instead of in:
Select ID from t where num between 1 and 3
6. The following query will also cause a full table scan:
Select ID from t where name like '%abc% '
To be more efficient, consider full-text indexing.
How to improve SQL query speed