Usage

view: my_view {  derived_table: {    indexes: ["order_id"]    ...  }}

Definition

Theindexes parameter lets you apply indexes to the columns of apersistent derived table (PDT) or anaggregate table. When you add more than one column, Looker will create one index for each column that you specify; it does not create a single, multi-column index. If theindexes parameter is missing from the query, Looker will warn you to add anindexes parameter to improve query performance. Learn more about indexing persistent derived tables on theDerived tables in Looker documentation page.

See theDialect support forindexes section on this page for the list of dialects that supportindexes.

Theindexes parameter works only with tables that arepersistent, such as PDTs and aggregate tables.indexes is not supported forderived tables without a persistence strategy.

In addition, theindexes parameter is not supported for derived tables that are defined usingcreate_process orsql_create.

If you useindexes with Redshift, you will create an interleaved sort key. You can also create regular sort keys usingsortkeys, but you cannot use both at the same time. Distribution keys can be created withdistribution.

Generally speaking, indexes should be applied to primary keys and date or time columns.

Examples

For a traditional database (for example, MySQL or Postgres), create acustomer_order_facts persistent derived table. The PDT should rebuild when theorder_datagroupdatagroup is triggered and will have an index oncustomer_id:

view: customer_order_facts {  derived_table: {    explore_source: order {      column: customer_id { field: order.customer_id }      column: lifetime_orders { field: order.lifetime_orders }    }    datagroup_trigger: order_datagroup    indexes: ["customer_id"]  }}

For a traditional database, create acustomer_order_facts persistent derived table that is based on a SQL query and applies an index oncustomer_id:

view: customer_order_facts {  derived_table: {    sql:      SELECT        customer_id,        COUNT(*) AS lifetime_orders      FROM        order      GROUP BY        customer_id ;;    persist_for: "24 hours"    indexes: ["customer_id"]  }}

For a traditional database, create acustomer_day_facts derived table with indexes on bothcustomer_id anddate:

view: customer_day_facts {  derived_table: {    sql:      SELECT        customer_id,        DATE(order_time) AS date,        COUNT(*) AS num_orders      FROM        order      GROUP BY        customer_id ;;    persist_for: "24 hours"    indexes: ["customer_id", "date"]  }}

For a Redshift database, create acustomer_day_facts derived table with an interleaved sort key built fromcustomer_id anddate:

view: customer_day_facts {  derived_table: {    sql:      SELECT        customer_id,        DATE(order_time) AS date,        COUNT(*) AS num_orders      FROM        order      GROUP BY        customer_id ;;    persist_for: "24 hours"    indexes: ["customer_id", "date"]  }}

Dialect support forindexes

The ability to useindexes depends on the database dialect your Looker connection is using. If you are working with something other than a traditional database (for example, MySQL or Postgres), your database may not support theindexes parameter. Looker will warn you if this is the case. You can swap out theindexes parameter for one that is appropriate for your database connection. Learn more about such parameters on theView parameters documentation page.

In the latest release of Looker, the following dialects supportindexes: