< one >sql optimization 1-4

First: Remove any scalar functions written on the predicate column
---> It is possible to use scalar functions on a select display column. However, the use of functions for columns in the filter conditions section after the where statement needs to be considered. Because the engine that executes SQL discards the index that uses the column because of the scalar function. resulting in a full table scan, performance degradation.
--->db V9 can use an expression index, but it is not recommended to write such SQL, the index maintenance cost is large
---> Examples
Select COUNT (id) from Tbl_nmc_paymentprocess p where year (p.requestdate) =2015
Overwrite to:
Select COUNT (id) from Tbl_nmc_paymentprocess p where p.requestdate between ' 2015-01-01 00:00:00 ' and ' 2015-12-31 23:59:5 9 '


Article two: Remove any mathematical operations written on the predicate column
---> It is possible to use mathematical operations on select display columns. However, using a mathematical operation on the predicate column after the WHERE statement, the predicate column becomes a non-indexed column.
--->db V9 You can create an index on a column by using mathematical operations. But not recommended
---> Examples:
Select P.confirmamount from Tbl_nmc_paymentprocess p where p.confirmamount*100>1000
Overwrite to:
Select P.confirmamount from Tbl_nmc_paymentprocess p where p.confirmamount>1000/100

Third: The display column of the SELECT statement, write only the necessary columns, the extra useless columns do not need to write.
---> Reduce the overhead of Io, sort the cost


Fourth: As far as possible without distinct remove duplicates
--->distinct is likely to make the query result set complete once, resulting in an excessive cost. It is also the most cost-effective function in SQL
---> Use GROUP by to remove duplicates and rewrite the query using in or exists subqueries.
---> Examples:
Select DISTINCT p.id,pp.id,pp. Paymentid from Tbl_nmc_payment P joins Tbl_nmc_paymentprocess pp on p.id=pp. Paymentid where p.finishdate>= ' 2014-02-18 00:00:00 ' and p.finishdate< ' 2015-02-18 23:59:59 '
Overwrite to:
Select P.id,count (pp.id) from Tbl_nmc_payment P joins Tbl_nmc_paymentprocess pp on p.id=pp. Paymentid where p.finishdate>= ' 2014-02-18 00:00:00 ' and p.finishdate< ' 2015-02-18 23:59:59 ' GROUP by P.ID
Overwrite to:
Select P.id from Tbl_nmc_payment p where exists (select pp. Paymentid from Tbl_nmc_paymentprocess pp where p.id=pp. Paymentid)
Overwrite to:
Select P.id from Tbl_nmc_payment p where p.id in (select P.id from Tbl_nmc_payment P joins Tbl_nmc_paymentprocess pp on p.id =pp. Paymentid where p.finishdate>= ' 2014-02-18 00:00:00 ' and p.finishdate< ' 2015-02-18 23:59:59 ')

< one >sql optimization 1-4