5 Well-Known DB Performance Tuning Tips (That Don’t Work At All)

Have you ever optimized a database and felt like the tuning is not working? It could be you are doing one or more of the following database performance tuning techniques.

Here are some 5 well-known database performance tuning myths that it appears, for now, have been debunked.

#5. If the performance sucks, put an index on it

Indexes are great for retrieving data from tables that have a lot of records. They speed up select queries.

But actually…

In the name of Create, Read, Update, and Delete (CRUD), Read or SELECT is only ¼ of the pie. Indexes affect the performance of create, read, and delete. You need to consider the effects of the indexes on create, update and delete operations before you decide to use them.

#4. If it’s slow, just optimize using 101 optimization tips

It is a common practice to rush to optimizing the database if the performance sucks. We have all heard of performance tips so we just have to implement them.

But actually…

What exactly are you optimizing? Have you done profiling to discover what is causing the performance to suck?

#3. Performance tuning should be focused on the database and not application level

Hey Mark, the system is slow. What’s up with that?

Mar: Let me check with the DBA, she probably needs to optimize the database

But actually…

You have code that creates views on the fly and they are not optimized for performance. You are using SELECT * as opposed to SELECT field names. Even when you use field names, you include columns that you do not need etc.

Optimization needs to be considered both at the application and database levels.

#2. You assume HAVING and WHERE are the same.

What is the difference between WHERE and HAVING? HAVING is used when you are dealing with a GROUP BY

But actually…

WHERE filter is used before retrieving the data. HAVING clause returns all the data, applies the filter then returns the results. This makes WHERE clause faster than HAVING.

If you do not have a good reason why you should use WHERE over HAVING then you probably need to use WHERE.

#1. SELECT field1, field2, field3 performs better that SELECT *

Using * when selecting database takes more time because the server needs to figure out the field names.

But actually…

The above is correct but return all the columns using field names still hurts the performance of the database. The key is to use specific field names and only return the relevant columns.