Using SQL Properly
Written 20 Oct 2004
<p>With the flurry of people that became web developers the past few years, quite a few gained their knowledge and skill using the time-proven hands-on method. This enabled not a few people to gain skill and knowledge needed for web development. One unfortunate draw-back to this however is incomplete knowledge. Oft times people are not <em>aware</em> of what knowledge is lacking. All that is needed is the manual, a support forum or mailing list for questions, and anything can be accomplished.</p>
That is not the case. Examining the plethora of web applications that exist, using sub-optimal designs and atrocious syntax, is evidence of that. Recently, O'reilly published an article that addresses one deficiency: SQL statements. Here is the synaspis:
<blockquote><p> <a href="http://www.onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html">How to Misuse SQL's FROM Clause</a> by Stéphane Faroult -- Many SQL queries misuse their FROM clauses. Misuse them? That's right. Stéphane Faroult explains this common mistake and demonstrates how two types of subqueries can improve performance and reliability.
</p></blockquote>
The SQL in the article is specific to the MySQLRDBM but the principles, the meat, of the article is the same: write your queries properly.
Examining SQL statements I wrote while learning reveals similar mistakes. Selecting all when only a subset is needed, doing one huge join of several tables then using the programming language to filter, not using available indices, &c.
At this juncture the point must be made: some of these learning deficiences must be laid at feet of the tool. The reason for this claim? Not until recently did MySQL support many necessary functions, such as sub-selects, that allows one to write proper SQL statements that execute quickly. Many web hosts still use the 3.2x version of MySQL, limiting what a developer can do. Further argument along these lines are best for another post though.