A web developer’s diary

November 14, 2006

Optimizing SQL for better performance

Filed under: General — Celia @ 6:02 am

One of the basic nightmares of a web developer is optimizing queries. Every now and then, I keep seeing articles which advise lesser queries per web page, normalised tables, indexes on the most queried columns and a diligent logic.

Ofcourse, this is what is needed. But at the same time, not everyone is exposed to better optimization techniques. If you are one of those, who have written a perfect SQL but still wondering why it takes that longer for it to get executed, read on.


Are you using the right technique :
This is the most important aspect of any programming/optimizing technique and often the most overlooked aspect. My friend uses PHP just for printing data in loops. Almost all his logic gets done in the backend. Formatting data, executional statements etc. While am not saying that Mysql can’t format data or include IF clauses, one also needs to consider what is best for what while coding.

It is always better to do all formatting stuff in the front end and use Mysql for fetching data.

Joins Vs Subqueries:

If your complex query includes a subquery, check whether it could be optimized by using a join. Joins are fast, and give better performance.

Indexing:

Yes, this is a must for any RDBMS. Whenever you are searching for some topic in your book, didn’t you always refer to the index? That gave you quicker access to the topic. Ain’t it?

Similarly, by indexing the database columns you gain speed. Always index the columns that are used more frequently in your where clauses and in your select clause.

Redundancy Vs Normalisation:

There is so much debate on this topic and both are good if used properly. Normalising your content is good. But at the same time, it is also good to hold summarized data in summary tables. Particularly, if your project does more querying than inserting. That would speed up your report queries.

Optimize:

Once in a while, use OPTIMIZE table command to optimize your table.

Last but not the least, if you come across better optimization techniques, do let me know 🙂

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: