NOTE: iContact Labs articles are written specifically for Designers, Software Developers and Engineers, and may contain industry terms not typically used outside of the industry. If you have any inquiries pertaining to this article, please contact the author for more information.

This post is part of a series. Read the first four articles Hunting for Database Hotspots, Part 1, Hunting for Database Hotspots, Part 2, Hunting for Database Hotspots, Part 3 and Hunting for Database Hotspots, Part 4 if you missed them.

Last time, we covered how to optimize the application to avoid calls to the database. A cache can offer a solution when you can neither avoid getting the data nor make the queries more efficient.

A cache serves as a logical middle layer between the application and the database.

Leveraging some agility and creativity can improve performance and reduce load on your database.

A great example of approximate data shows up when you do any search through your favorite search engine. Both the number of search results and the number of pages are approximations. Sometimes you’ll find as you switch from page 3 of search results to page 4, the search engine finds there aren’t actually any more results to show. Alternatively, as you page through the results, the number of pages can decrease. In both of these cases, the performance and accuracy of the information is tuned to the needs of the users.

Data shows that more than 50% of users only click on the first search result after conducting their search. Under 10% of users voyage beyond the first page of search results. To more than 90% of users, the many pages of results beyond the first are irrelevant. Search engines have realized this and prioritized the computation of these values.

An application’s performance can often be improved in many ways, but query optimization should always be near the top of your toolkit. Databases are designed to optimize many cases on their own, yet we know that by asking them questions in different ways and changing the structure of data, performance can vary tremendously!

The query itself – Can it be improved? Can you reduce the number of joins, return less fields, do less sorting, avoid pagination, avoid generating temporary tables, make better use of existing indexes? MySQL’s EXPLAIN is your friend to help see what you should improve about your queries.