Indexes And Data Access Patterns

Financial Advice

When I talk about “data access patterns” in relation to databases, it’s often met with blank stares.

What is a data access pattern? What does it have to do with indexes?

Well, my dear friend, today you’re gonna learn it good and hard.

Medical Doctor

Data access patterns refer to the most common ways that queries filter, join, order, and display data.

The earliest point of a data access pattern is your where clause. Different queries may have different patterns of data to look for.

Giving some generic OLTP-ish examples, you might have queries that look for:

  • Customer orders
    • Within a date range
    • Ordered by most recent
  • Items in a customer order
    • With the total price
    • Plus shipping
    • Plus tax
  • Items in stock
    • Total quantity

Depending on how normalized your data is, getting some of this stuff will likely require 2-3 tables getting joins together in some manner.

But all of these different scenarios define your data access patterns, and this is how you need to gear your indexes.

A lot of people get caught up on the minutiae of indexes without taking care of any of the basics, worrying about foreign keys, GUIDs, fragmentation, and other ridiculous memes.

Personal Trainer

Your data access patterns should define your indexes, because that — along with well-written queries to use those indexes most efficiently — is what’s going to make your application fast.

I’ve blogged about some of the fundamental concepts behind this in the past:

If you still need help with your index design after reading those, drop me a line! That’s the kind of thing I love helping people out with.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *