MY SQL Query hints , best practices and performance

Hi,

 Below described some SQL practices which are important to us during the API development,

1.            Use ASC or DESC when you are using ‘LIMIT’ in the Query, Sometimes you may not get desired results

2.            Distinct not work only for selected column, but eliminates duplicate rows,

Ex :

nativeQuery.append("SELECT ");

nativeQuery.append("DISTINCT(payment_reference_no.id),");  

nativeQuery.append("(\\@cnt \\:\\= \\@cnt \\+ 1) AS rowNumber, ");

nativeQuery.append("payment_reference_no.created_at AS  prnCreatedAt, ");

3.            Use always parametrized query, caching will improve performance

4.            Need, full group by columns after the use aggregate functions [depends on where clause]

5.            IF foreign key is not null ,use JOIN[inner join] not the LEFT JOIN,..LEFT JOIN results sometimes may not be corrected and have performance hit.

6.            IF you are using LEFT JOIN , do not add relevant table's filter criteria to where clause it should be with JOIN condition .

7.use concat_ws function to use for separate concat strings to given for mat

Ex concat_we('-' ,'aa','bb');

aa-bb  

8.use always nested query joins