DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. How Database Indexes Really Work

How Database Indexes Really Work

Let's take a look at how database indexes really work as well as explore what a database index is and why databases need them.

Katy Farmer user avatar by
Katy Farmer
·
Oct. 12, 18 · Opinion
Like (10)
Save
Tweet
Share
12.81K Views

Join the DZone community and get the full member experience.

Join For Free

I have a growing love of databases that leads me to ask a lot of questions about how they work, and my recent obsession is database indexes.

Previously, I knew that if I wanted a particular column or field to be faster than the rest of them, I indexed it. That was as much as my brain could handle when I was first learning to code, but growing as a developer means expanding my knowledge of the fundamentals, like what exactly a database index is and why it exists.

Image title

There are lots of ways to grow as a developer

What Is a Database Index?

Imagine we're walking through San Francisco looking for our friend Chris. We know she lives in San Francisco, but we don't know her address. While each building has a unique address that allows us to find it easily, we need a way to tie the person we're looking for, Chris, with her unique address. In this case, we have to knock on every door in San Francisco until we find her. That's not very efficient (and probably not a good idea). But if we had a directory tying Chris to her address, 123 Nunya Lane, we could walk straight there.

This is the same principle as a database index. A database index is a type of data structure, like an array or a hash. It's just one way we can organize data. In this example, we would have an index of names that pointed to addresses.

Image title

You never know who will answer the door in SF

So why do we need a data structure inside of our database, which is, you know, a big data structure?

Why Do Databases Need Indexes?

We keep so much stuff in our databases-literally anything we think we might need later, from user credentials all the way to the latitude and longitude of the pizza being delivered to our house. Without an index, the database is stuck knocking on every door in San Francisco or searching through every record in a linear fashion. Sometimes, this works just fine. Then again, some databases store hundreds of millions of records, so searching linearly could take ten steps or 525,600 or 300 million. We have to consider the potential number of steps. Without indexes, the database can end up with extremely slow queries as it searches each record for a match, which can then cause a buildup of waiting queries. Latency and overall response time would increase, and anyone waiting on the results of those queries either has to get a hobby, or more likely, use a different application.

What Should Be Indexed?

When deciding to add indexes to our database, we need to consider our data. Indexing every column or field can also have negative effects. If we create ten indexes, writing a single record to the database turns into 11 writes: one to the database, and one to each of the indexes (assuming that record includes all of the indexed columns/fields). As a guiding principle, we want to index the data that is looked up most frequently. The cost of writing to the index is offset by the improved performance of a significant number of our database queries.

What Is the Difference Between Relational Database Indexes and NoSQL Database Indexes?

In relational databases, indexes are created by column. We can choose any column or even a combination of columns to create our index.

NoSQL databases can have indexes, too! There is far less convention in the world of non-relational databases because of the variety of databases, but most of them have excellent docs on how to index data. I like InfluxDB's indexing in which data inserted as a tag is indexed and data inserted as a field is not, so I don't have to think about the index more than that if I don't want to.

Summary

Database indexes are a fundamental part of understanding how our databases spend time and resources, and creating them encourages us to understand more about our applications and the data they produce. I feel smarter already.

Database Relational database

Published at DZone with permission of Katy Farmer, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • The Top 3 Challenges Facing Engineering Leaders Today—And How to Overcome Them
  • Simulating and Troubleshooting StackOverflowError in Kotlin
  • 5 Tips for Optimizing Your React App’s Performance
  • Efficiently Computing Permissions at Scale: Our Engineering Approach

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: