5 Database Lessons I learnt the hard way.

Some of the lessons and patterns that are called standards or best practices, came as result of several years of experiences and in some cases, studies. I have had my fair share of tough lessons that I learnt the hard way. Below are a couple of the lessons, specifically on Database.

• Avoid over-indexing.

Indexes are powerful tools for speeding up data reading and retrieval. They work by creating an extra structure that the database can use to query data. However, over-indexing is wrong as indexing requires additional storage and this will consequently slow down write operations. Create indexes on columns used in search conditions like (WHERE, JOIN), and sorting (ORDER BY).

• Query structure impacts performance.

Complex queries with subqueries and nested logic will force the database to work more. Sometimes, it makes sense to run separate queries and add them on the server side. Selecting only the necessary columns reduces the database's data to process and transfer.

• Pay attention to JOINs and WHEREs.

Joins combine rows from two or more tables based on a related column. WHERE filters records. JOINs and WHEREs clauses can reduce the rows one needs to process in later stages of the query. Write the WHEREs, and JOINs taking advantage of the indexes.

• Subqueries need careful handling.

Correlated subqueries execute for each row in the outer query, leading to inefficiency. Rewriting subqueries as JOINs or using temporary tables will improve performance.

• Data structure affects query performance.

Data normalization involves fixing the state of the database by organizing the database schema to reduce redundancy and improve data integrity. But it can lead to complex queries with many JOINs. Denormalization simplifies queries at the expense of increased data redundancy.

What else in your experience should be added to the list? Please comment it.

Thank you for your time and see you in the next one.