Performance Optimization

Hi folks,

Improving the performance of a website has haunted the programmers for a long long time. In this post I would try to make it a little easy. But before you could start making super fast websites, you need to know how page request and response takes place. Because code might not be the only culprit in every case for slow websites.

Here are few reasons which might result in slow websites:

1. SQL Queries
2. Non indexed database tables
3. API calls in looping sequences

The above reasons will result in slow response from the server. During this you will keep on seeing "waiting for www.yoursite.com" in the status bar of your browser for a long time.

Finally, when the "waiting.." stage is over but still the actual page takes time to get downloaded in the browser, then following might be the reason:

4. Heavy CSS and Javascript files
5. Too many images
6. Extensive DHTML processing loops

In this post I will discuss the 1st three points, remaining I will discuss in future posts. So coming to our first point:


1. SQL Queries


Most of the time un-optimized SQL queries result in slow websites. But it is very difficult to predict which query will take more time to execute in the initial or development stage. This is because, data is less. The quality of a query is understood when there are thousands of records in the database table. So its better to stick with the basic SQL rules during initial development phases.

Here are few tips to do that:
1. Avoid using 'IN' statements. e.g. 


    a. select t1.name,t1.age from t1 where t1.id in (select t2.id from t2) will always take more time than
    b. select t1.name,t1.age from t1,t2 where t1.id=t2.id

This is a very simple example. But it you have thousands of records changing from a to b can reduce about 4-5 seconds of server response.

Finally use query caching to improve the performance. This can be done by using memcache. Moreover I had developed a class for caching queries for improving performance. Click here to view that article.

2. Try to use left joins more than inner or outer.

3. NEVER use "*" in select queries for fetching columns. 
Use field name specifically which are needed. This is always considered as a good practice.

4. Use InnoDB database engine. It provides row locking instead of table locking unlike MyISAM(default database engine). This is another good practice. 

5. Last but not the least. Try avoiding the count() function as much possible. 


You have to think of solutions to achieve it out of the box. Suppose a scenario when you need to show product count besides the category name. e.g.

Shopping(25)
Books(19)
Kids(30)
etc.
Do not use count() function to find the numbers. One of the solution is to keep a counter field in the master category table. When a product is added or unblocked the counter value increases and decreases when a product is deleted or blocked. Putting this functionality wont be such a problem, but it will definitely save few seconds of response time

2. Non indexed database tables

Always keep your database table indexed. But the thing about indexes is, you need to know on which fields you want to create index. Well that is not a difficult thing. Just check your query which is taking time to execute. The fields which are in the "where" clause of the query, just index those fields of their respective tables. I am sure this will make a striking difference. In fact first thing you should do after schema design is indexing its fields.
Here is how you can create an index:

create index <name_of_index> on <name_of_table>(<name_of_field>(<field_size_if_text_data>))

3. API calls in looping sequences

This is another reason of slow responding websites. API calls such as generating short url's, if kept inside looping sequence can really slow down the system. Whenever a API call is made it is calling an external server, and if that is kept in a loop, it definitely will slow down the system.

Stay tuned for more posts on website optimization.

Hope this helped!