No one likes waiting. Not in line, not on hold, and certainly not for a web page to load. That’s why optimizing your website for speed is so important. Many things go into website optimization. In this article we will focus on how database queries can slow down your website if they are not done properly.
Bulky queries to your database can drastically slow down the speed of your website. This isn’t specific to MySQL, this problem is present in any website that is backed by a database. Normally when you go to a website, the web server serves you a page of HTML. That’s simple, small and quick. When you have a dynamic page done in PHP, ASP or JSP an extra step is added (this article assumes that PHP is the language being used). The web server must compile the code on the server and then serve the resulting page. When you have a dynamic page that queries a database, the server must compile the code, stop to query the database server, wait for a response, continue compiling the code, and then serve the page. Another added step.
The time it takes the server to serve up a page that requires one or more queries to the database server is negligible most of the time. Especially if the database server is hosted locally on your web server. However, with large, complicated queries, the time the web server must wait for the database server to run the query and return the result can go from negligible to intolerable.
This is mostly caused by poor structure in the queries made on the database. As a rule a query should never return more results then is needed, and should never return information that is not needed. This might seem like common sense, and indeed it is, but never the less, some developers do not take care when writing their SQL queries. The result is extended load times for the pages that use them.
Page load times can have a negative effect many things. First and foremost the patience of your visitor. Second and less well known is search engine optimization. Long load times can result in lower page ranks. Third and possibly even less well known is the effectiveness of your CPC (Cost per Click) advertisement campaigns. Google Adwords as well as many other online marketing tools take the load time of your landing page into account. Longer page load time can result in lower quality scores for your keywords.
Let us examine an example. You have a table in your database that holds information about various books. That information is as follows: Author Name, Purchase Price, Sale Price, Number of Copies in Stock, ISBN, Date Published, Publishing Company, Number of Pages, Blurb about the book.
On your home page you want to display a random book. The information about the book you want to display is: Author Name, Date Published, Blurb.
Some developers will take a shortcut when writing the query to the database and simply ask for all information about all the books that are stored in the table. Then they’ll pick a random book and the information they want about that book in their PHP code. This causes several things to happen. First, the query takes MUCH longer to execute because it has to return all the information about every book in the database. Second, the PHP code has to deal with the massive amount of information. Third, significant strain is put on both your web server and your database server. Fourth, the page takes much longer to load then it should. That is the fast way, not the right way.
Two things can be done right off the bat to optimize this query. First, instead of returning the entire tables worth of books, have the database server pick a book randomly and return only the information about that specific book. There is no need to use PHP to do something that the database server itself can do. Second, structure your SQL query so that you are asking for only the information you want. Author name, Date Published, Blurb. This significantly reduces the strain on your database server and increases the speed at which the query executes. It also increases the speed at which your PHP code can execute, as it is dealing with much less information. It also doesn’t have to randomize that information, or sift through it. It simply displays the result of the database query.
The example above may seem unrealistic, but it’s something we see time and time again when we take on new clients. Some developers charge such a small fee that if they took the time to develop your site properly they wouldn’t make any money. That is the proverbial catch.
There are many things that must be taken into account when your site is being developed for it to be as optimized as possible. Properly structured and thought out database queries are just the tip of the iceberg.