Web Books

SQL Optimization

Written by: Matt David
Reviewed by: Blake Barnhill

Get exposed to the most common techniques for improving query speed and database performance. Learn how to optimize queries by modifying SQL. Understand how indexes work and when to apply them. Learn the basics of data modeling and how it impacts performance. Understand the optimization techniques available in modern data warehouses.

Query Optimizations

• What is a Query Plan 

  Query Plan's are the code that SQL runs on your database. Learn how the query plan works and how to optimize your SQL queries. Learn more

• Order of a SQL Query 

  Learn the order of the SQL query to understand where you can optimize a query. Learn to prioritize FROM, JOIN, and WHERE.

• Optimize your SQL Query 

  Learn quick tips for how to optimize your SQL queries

Column and Table Optimizations

• Optimization with EXPLAIN ANALYZE 

  The EXPLAIN shows the query plan for SQL queries in Postgres. Learn how to interpret the results from EXPLAIN and use it to optimize your SQL queries.

• Indexing 

  Indexing is a critical part of database optimization. Indexing can dramatically increase query speed. See how indexes work and learn to build indexes with SQL.

• Partial Indexes 

  Partial indexes are an advanced database optimization technique. Learn how to create Partial Indexes and measure their performance.

• Multicolumn Indexes 

  This article walks through what multicolumn indexes are, how to make them, and when to use them.

Modeling Data

• Start Modeling Data 

  Quick introduction on using to dbt and BigQuery to model data. Modeling data can have significance performance impacts on your database.

• Scheduling Modeling 

  Schedule data modeling tasks to make data easy to query. Learn to use dbt Cloud to model data to optimize your data warehouse. Learn more.

• Views 

  SQL VIEWs allow you to create temporary or permanent references to data. This is a common database optimization technique. Learn more.

Databases

• Redshift Optimization 

  Learn how Redshift manages optimization for your cloud data warehouse. Learn when to manually tune the database further for more performance for SQL queries.

• BigQuery Optimization 

  Learn how BigQuery optimizes your database through specific hardware optimization strategies.

• Snowflake Optimization 

  Snowflake is a powerful cloud database. Learn how it optimizes your database automatically, and hot to increase performance manually.