From the course: Designing Highly Scalable and Highly Available SQL Databases
Unlock the full course today
Join today to access over 22,400 courses taught by industry experts or purchase this course individually.
Indexing for transactional queries
From the course: Designing Highly Scalable and Highly Available SQL Databases
Indexing for transactional queries
- [Instructor] Now, as I mentioned, indexing is often used with transactional queries. And the reason we use indexing is it because it helps improve read performance. Basically we reduce the read latency. So the time it takes to actually find the data and retrieve it and then return it. In addition indexes are sometimes used to help enforce integrity constraints or any kind of logical constraints. So for example, a unique constraint on a column, it's very easy to look up in an index and see if a value is already in there. Now, the idea behind indexes is that we have pointers to where we should go look for the data. And that allows us to reduce the number of data blocks we have to scan. So like, if you imagine you open the filing cabinet drawer and you had to start at the beginning of the first file and look at every file in the drawer to find what you're looking for, that's going to take you much longer than if you knew…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
Transactional vs. analytical queries5m 41s
-
Indexing for transactional queries10m 11s
-
Materialized views for transactional queries3m 51s
-
Using read replicas to improve query performance2m 55s
-
Understanding write-ahead logging5m 6s
-
Denormalizing for analytical queries4m 18s
-
Aggregation and sampling for analytical queries5m 45s
-
Challenge: Optimize a data model for an analytical queries25s
-
Solution: Optimize a data model for an analytical queries35s
-
-