How to optimize SQL queries

Tram Ho

With a large number of records in the DB in software related to many businesses, the essential method for guaranteed system speed is to optimize each SQL statement. Here I would like to introduce some tips to optimize SQL statements

1. Try not to use select * to query the SQL, but we’ll use select specific fields

Usually, common, normal

Better way

By using only required fields, we can save our resources

2. If you know that there is only one query result, limit 1 should be used

Usually, common, normal

Better way

After adding limit 1, when a corresponding record is found, it will not continue to scan down, and the efficiency will be greatly improved.

3. Limit the use of or in the where clause to conditional queries

Usually, common, normal

Better way

Using or can invalidate the index and therefore require a full table scan.

4, Optimize limit with offset index

Usually, common, normal

Better way

If you use solution1, it will return the last query record (offset), so that you can ignore the offset, the efficiency is greatly improved. Option two uses the order by + index, can also improve query efficiency.

5 Optimize like commands

Usually, common, normal

Better way

Where% 123 will scan the entire table in case of 123%, the optimizer is using the index we created and doing a search instead of scanning

6. Use where conditions to limit the data to be queried to avoid returning redundant rows

Usually, common, normal

Better way

Check what data is needed, avoid unnecessary data return and save money

7 You should avoid using operators! = Or <> in where clause as much as possible

Usually, common, normal

Better way

Use! = And <> is likely to invalidate the index

8. Use distinct with care

The distinct keyword is often used to filter duplicate records to return unique records. When used in the case of querying a single field or several fields, it is optimized for the query. However, when there are many fields, it will significantly reduce the efficiency of the Regular query

Better way

The CPU time and time occupied by the command to distinct are higher than non distinct statements

9. Eliminate redundant and duplicate indexes

Usually, common, normal

Better way

Duplicate indexes need to be maintained, and the optimizer needs to look at them one by one when optimizing queries, which will affect performance.

10 Consider using default values ​​instead of null in where clause

Usually, common, normal

Better way

Above are a few ways for you to optimize your queries, you can have more ideas about optimizing and speeding up your SQL queries.

Reference source:

https://towardsdatascience.com/how-to-optimize-sql-queries-742177cd5cc6?gi=a4b2e25af429

Share the news now

Source : Viblo