If you're serious about web performance optimization, then you must run an efficient database. One necessary skill is an ability to analyze a database slow query log and optimize the most expensive, slowest results. While one relatively slow query may not be the end of the world, many slow queries will add up quickly and frustrate users.

Setting Up a Slow Query Log

Running a slow query log is not a default setting in MySQL, so the first thing you’ll need to do is set one up. Open the server’s configuration file and enter “log-slow-queries” as well as an output file. On the next line, enter “long_query_time=x”. The value x represents a certain number of seconds, and any query taking longer than that number will appear in the output log.

Although needs will vary, a value of 1 is generally recognized as a good starting point. For heavily used servers, a value of 1 may result in a slow query log which is too large, creating additional performance problems.

In addition, you may want add a third line which reads “log-queries-not-using-indexes”. (Evidently the creators of MySQL did not realize that the plural of "index" is actually "indices.") This will instruct MySQL to log any query which does not use an index, whether or not the query time exceeds the value listed on the second line. If you only want to log queries not using indices, you can set the value on the second line very high.

Whenever you have a large scale database with multiple tables and numerous requests from users, building tables with perfect indexing can be the difference between great performance or angry users with time outs. Table indexing is often ignored while designing an application. It is usually left to the database administrators to pick up the pieces after an application is promoted and poor indexing causes performance issues. As a great developer, you can avoid the pitfalls of poor table indexing and create a tuned application even on the database backend.

Primary Keys

For most developers, worrying about optimization when writing SQL queries is not an issue until performance becomes a problem with an application. Although SQL query optimization is important, it can be seen as a tedious process that some programmers fail to follow thoroughly. Some best practices can be observed during the development process that will help maximize performance for future application scalability.

Use SELECT Instead of Using SELECT *

One of the biggest issues a database administrator deals with is the impending performance issues that seem to hit an expanding environment. Smaller businesses can usually handle the simple problems that may interrupt daily activities, but large or medium sized businesses need real solutions to quickly fix problems since performance issues equal lower revenue.

Microsoft SQL Server 2000 and Microsoft SQL Server 2005 do not inherently support load balancing out of the box like most information technology experts might think. They have intrinsic tools that allow for scalability and load balancing, however, they do not have the defined load balancing functions that are commonly known among network administrators. Instead, Microsoft SQL Server 2005 comes with some tools to help with scalability and performance for your enterprise organization.

Microsoft Clustering Services (MSCS)