How to improve performace of a Website ?

Evaluation of website is an important step in any performance aspect. Its level of aspect varies from application to application, based on a variety of factors (such as application at code level, architecture and databases). It has been an issue to pass judgment on the performance hurts either on database level or in application layer for many years. Here, I’m trying to focus on both levels.

Application Layer

Followings are some practices in my opinion through which we can improve the performance of web at application level.

• Select only required column

Don't query columns that you don't need in your application. We use entire table fields in most of the case by using active records.

• Avoid Group By, Distinct , Not In

These will create temporary tables on database which is major performance headache; these can be handling on application layer via server side scripting.

• Data Types

While creating a query at application, I have noticed many times, data types which are expected are not provided, Like When an integer is expected, it’s given under apostrophes’.  Data type length and type must be provided accurately.

• LIMIT

When Getting a Unique Row use limit. This way the database engine will stop scanning for records after it finds just 1, instead of going through the whole table or index.

• Fixed-length (Static) Tables

These are faster; it can be apply by replacing VARCHAR, TEXT, BLOB.

Database Layer

Followings are some highlighted issues for improvement.

• indexes

 Apply on primary key which are used on joins, frequently in search criteria and Used as foreign key fields. It will significantly improve the performance especially on big tables like practitioners.

• Default Values

Set default value rather than Null. Null means missing unknown value. It cannot use the arithmetic comparison operators such as =, .

• Use short primary keys

I’ve noticed, long keys are used on many times of length 11 digits, which is avoidable.

• Appropriate Data Types

Use integer field more as you can instead of string and varchar instead of text type.

• Use of Mysql functions

Avoid Mysql costly functions on queries; these can be handled on application layer.

• Correlated queries

Correlated queries also create temporary tables these can be handle by dividing the query into two queries, execute inner query as a separated request and provided the result to outer query via server side scripting.

Other Experiments which can enhance performance

• Persistent connections

Application of persistent connections on cross database scheme if its required

• skip-locking

It will improve SELECTs but has an overhead of INSERT/UPDATES.

• Partitioning

Partitioning would be helpful for big tables

Please add your comments if you have more suggestions.