How not to build a DB (One Efficient Database Design Principles)

If you are building a project that will eventually have huge records in your database, for example 7,000+ rows, you need to learn the indexing principles.

This one principle may not cause any significant or noticeable effect if your database record is less than 1,000/5,000. But it will cause noticeable slow performance once your record starts increasing past that, making your database query slower than when you have just 10 rows.

I'm sharing this because I had to optimize and redesign the database of one of my earliest projects. Seeing my database design was funny because then I couldn't factor in these things, mostly because I've never seen or tested my queries performance on a database with thousands or millions of records, just like many junior developers currently building.
If you work with a database, read this!

What you will learn about:

• Indexing
• How to choose the right column to add an index

What is indexing a column? (what it means to add an index to a column)

A database, after storing your table's records, it then saves another separate data structure that is organized in a way that allows the database to quickly locate records much more efficiently rather than scanning the entire table row by row.

SO instead of looping through the records row by row until it finds the match, this data structure helps the database management system easily locate rows.

You remember those search and sorting algorithms you learned about or keep hearing about? Yes, this is one of the places where those algorithms are used. So understanding them gives you a guide of how the index improves your query performance.

Proper Indexing

To properly add an index to the right columns, you first need to answer these questions:

• How often will I be fetching records using this column name in the WHERE clause? If the answer is most times, then add an index to it.

If the column frequently appears in WHERE, JOIN ... ON, ORDER BY and GROUP BY, then it should be indexed.

• Will the column contain values that will mostly be the same? If yes, don't index it?

• Do you frequently sort by the column?
  An index on the created_at column could potentially help the database efficiently retrieve rows in a required order.

• Looking at my application system design, what does it say?

Example 1 (without an index)

"Find shanks's test result record" 
→ database potentially checks row 1 
→ row 2 → row 3 
→ ... until it finds shank test result.

Example 1 (With an index)

"Find shanks's test result record" 
→ database uses the index (maybe userId, email column) to quickly determine where shanks's record is 
→ retrieves the row.

Now that you understand the core concept, you just leveled up.

Adding an index to a column on any table is usually easy. You can use SQL or add it through the ORM you are already using or the indexing method provided by your particular database.

Study resource: System Design Interview by Alex Xu