Some simple tips for optimizing database statements

1. Index

A. The fields used in the where and join parts of the SQL statement should be indexed.

B. Try to use indexed fields for direct judgment, do not convert the indexed fields before you judge, because this will result in the addition of the index is invalid, the effect of optimization is not achieved.

e.g. querying all data in table A for all 2016 years

CREATE INDEX a_idx on A (Date_column); SELECT text, date_column from  A WHERE to_char (date_column, ' YYYY ') = ' 2016 ';

The above index Date_column does not take effect, this query can be changed to compare the scope of date_column.

SELECT text, date_column from  A WHERE date_column >= to_date (' 2016-01-01 ', ' yyyy-mm-dd ') and   Date_column <  to_date (' 2016-01-01 ', ' yyyy-mm-dd ');

C. Build the index to cover your SQL statements as much as possible.

e.g. the following 2 query statements all use the B field in table A

CREATE INDEX A_idx on a (a, b); SELECT ID, a, b from  a WHERE a =: A and   B =: b; SELECT ID, a, b from  a WHERE B =: b;

The index cover the first SELECT statement, but the second SQL statement does not use the index to the fullest extent. You can modify the index to achieve maximum utilization.

CREATE INDEX a_idx on A (b, a);

D. Indexed fields, if the like wildcard filter some strings, the index is not valid at this time.

CREATE INDEX a_idx on A (text); SELECT ID, text from  A WHERE text like '%term% ';

Note: If all the referenced columns in the entire query are included in the index, then the query does not require direct access to the table, and the query speed increases.

2. removing unwanted tables and fields

Do not return unwanted fields to the SQL client.

Do not query for tables that are not needed in SQL statements .

3. Remove External links

4. try not to compare the fields before converting them .

It is more efficient to query the original field directly than after the conversion. For example, this article is the first SQL statement to query 2016 data.

Some simple tips for optimizing database statements