Recent Articles |
Ingres Christens Project Icebreaker
The open source database company Ingres teamed with another open source player to deliver Icebreaker, a way to place database services on a server with no operating system required. Ingres CTO Dave Dargo blogged about Icebreaker...
MySQL Turbo Manager And Dream Coder
The success and the longevity of any prominent business nowadays depend on powerful application infrastructures and effective, reliable management and development solutions.
SQL Replication
Database management systems are very important for business today. Whether your application has a network access to all employees in your organization or whether it's a web application. The more the number of users supported by the application the more will be the load on the server...
Your Old MySQL Needs An Update
Versions 3.23 and 4.0 of the popular open source database will soon reach their end-of-life dates this year. MySQL will see off support for older versions of the database this year. InfoWorld reported August 1st will be the end-of-life for version 3.23, while October 1st marks the end of version 4.0.
PHP Pagination with MySQL
It is extremely common these days to make results display across multiple pages. Some examples are maybe browsing through picture galleries, store products, blog entries, etc.
|
|
|
09.12.06
Practice Makes Perfect For SQL
 By David A. Utter
MySQL guru Sheeri Kritzer listed eight SQL best practices for database professionals who are hard at work on their projects.
Kritzer's list began with a preface on why she tries to live by the eight SQL rules in her post about best practices:
In declarative languages like SQL, you program what you want the result to be, not the procedure to get it. For instance, "give me all the people with the first name starting with the letter S from a certain table."
Unlike procedural programming (or even methods in object-oriented languages), you do not say how to get the information. This is, I believe, why many developers want to give the query optimizer "hints" on how to do its job.
Kritzer started the list by suggesting database developers always use explicit joins. "If I mean INNER JOIN, then I use INNER JOIN. No use of just plain "JOIN". Never, ever, ever use a comma join - I consider that a mistake," she wrote.
Wanna wallet stuffed with $10,000?
SalesGenius.  |
|
One person asked about that practice in a comment about the post. Peter Zaitsev of the MySQL Performance Blog said this:
I personally feel this is syntax matter, unless you use LEFT JOIN. Some people find it more readable, some not - I would for example like to see clauses grouped by tables as for inner join there is no difference between where and on clauses for optimizer and I prefer to see how query can be executed easily.
Kritzer agreed in her response that the use of explicit joins is definitely syntax. "I feel that specifying INNER vs. LEFT or RIGHT or CROSS helps them understand that they're not saying "I want you to join the tables," ie, take an action, they're saying, "I want the result if you join the tables this way," ie, "this is what I want back."
Database pros should not be afraid of JOINS. If good indexing has been used for the database, Krtizer said JOINS are not necessarily resource-intensive. And when a correlated subquery can be replaced with a JOIN, it should be replaced.
Developers should also always define field names. "No using SELECT * or INSERT INTO table VALUES. It's a pain," she wrote. For reporting, always use the timestamp from the database server, in case those of web servers are different.
Reporting will go more smoothly if one stores IPs as integers with INET_ATON and retrieves them with INET_NTOA, Kritzer also wrote. On the topic of reporting, she considered the impact of network traffic and made this best practice suggestion:
If you're going to receive information, it's better to receive in chunks, which will likely be larger than a logical piece. For instance, state reporting - instead of making 50 connections for states in the US, get them all at once.
If the dataset is very large and folks do not want to stare at a blank page while the report is loading, use paging with LIMIT to grab, say, 1000 entries at a time and display them on the screen so people can start looking at the data while the rest is being grabbed.
"Running a query in a loop is usually a bad idea," Kritzer said of those. "Consider building a query string using UNION and executing it at the end of the loop, so you can execute multiple queries with only one trip across the network to the database."
About the Author:
David Utter is a staff writer for WebProNews covering technology and business.
|
