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

 

 

 

 

 

UI User perspective hints

Hi,

User perspective below points may important before UAT/Demo

1.Tabout UI function for all inputs

2.Enter key form submission

3.Enter key search functions

4.Simple UIs for most functioning UIs – minimise user clicks for achieve some task

6.UI size – may not accept scrolling

7.Uniqueness of the screens components – buttons, input field…

8.May request Searchable combo box instead of drop down when considerable data is exists,

9.minimize horizontal scroll in tables

10.Ask confirmation when click a menu item when working with some page

Design DB

 Database Design concepts
*Tables names should not be plural
 column should meaning full
*Try natural key to use as primary key maximum , minimize surrogate key.
*Natural key can be found from business and should be finalized for future
* Use code for meta and master tables primary key, if not must use code unique and foreign key for other tables
foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table:
*Use naming convention for master and transaction tables
mst_
txn_ or _txn
*Define constraint

unique, index, check ....

*MySql  - Relatoional Database, using foreign key with primary key and unique key .

*Don't forget to focus on data quality and integrity - do not duplicate same data everywahre

( You shouldn't have tables designed to store the same information in multiple locations)

Use
triggers ,views....

write parameterized queries in the application - views may use for that

Some important points for the MySql Database

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

2.      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.     3.  Use always parametrised query [Already explained by Prageeth]

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

5.     5.  IF foreign key is not null ,use JOIN[inner join] not the LEFT JOIN,…