This article will walk you through the factors that can impact query performance in Looker. Because query performance is strongly tied to the underlying database, these options will require the assistance of the personnel who set up the database system.

Declaring Indexes

Many queries will greatly benefit from the addition of proper indexes. You can look at the indexes of each table directly in Looker from our SQL runner page, by clicking on the small cog on the tables, then selecting Show Indexes (see image below).

Important dates and foreign keys are the most common columns that can benefit from an index. Adding indexes to these columns will increase performance for almost all queries. This also applies for persistent derived tables. LookML has parameters that allow you to define indexes, sort keys, and distribution keys as appropriate.

Database Engine Configuration

Some databases allow different underlying database engines. For example, MySQL uses InnoDB and MyISAM as the default engine.  InnoDB is also optimized for operational applications that require high frequency transactions.

For analysis, we don’t really need to worry about this feature set up, and are OK with table locking as long as our queries run faster. For this reason, MyISAM is often a faster engine for MySQL analytics. Updates will occur when no queries are being run.

Database Specs and Resource Allocation

Some databases do not have sufficient hardware (or in AWS, the necessary provisioned resources) to deal with the size of the datasets. It is recommended to increase memory, cores, and I/O (input/output) which will also increase performance of the queries.