Balancing the workload between the websever & database server

How to write content rich - optimized web applications quicky which does not over load the webserver?

Its a common habit of newbies to use SQL queries on the database just to fetch the records. Then use the functions provided by the Server side scripting language like ASP / PHP to process that result set and print the output. The next step would be to filter out records using the WHERE in the SQL.

As you gain experience as a web programmer, you learn to do wonders by writing effective SQLs.

Imagine you have a timestamp field on the DB. Don't you think its a lot of work & over head on the server to fetch the time stamp and process the timestamp using string function in a loop ?

Rather will it not be easy to use the following SQL:

SELECT DATE_FORMAT('format_string', ) FROM tablename

Also, you can optimize the performace of a web application by writing SQL JOINS. Use the DB server do the job and reduce the work over load on the web server & the server side scripting language. Check out the following SQL:


SELECT users.uname AS USERNAME, groups.name AS GROUPNAME, COUNT( * ) AS LOGINS, DATE_FORMAT( userlogintrack.ltime, '%b %D, %Y' ) AS Date
FROM userlogintrack
LEFT JOIN users ON users.uid = userlogintrack.uid
LEFT JOIN group_membership ON group_membership.uid = users.uid
LEFT JOIN groups ON groups.gid = group_membership.gid
GROUP BY userlogintrack.uid, TO_DAYS( userlogintrack.ltime )
ORDER BY Date DESC , users.uname
LIMIT 0 , 1000


The above SQL process 3 different tables and gives the result set to the server side scripting language (PHP) which can just print the results without any additional processing at the web server. If you were to do this with PHP alone, then you have to process 3 different result sets with increased overload on the busy webserver.

That being said, don't start writing complex SQLs and sacrifice the power of a server side scriptiong language. A good, efficient programmer has to strike the correct balance between the SQL & the functions of the server side scripting language to distribute the workload.

Writing SQL Sub Selects as JOINS...

Did you know that you can re write all sub-selects as SQL Joins? This comes handy when you are using a DB which does not support sub-selects (for eg. MySQL 3.x)

Take the following Sub-Select for instance:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

The above can be re written as:

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;

Now, consider this Sub Select:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);

this can be rewritten as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

Sub Selects/Joins help to distribute work load to the database server rather than filter data at the front end.