home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
indexes

Usage

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

Hierarchy

indexes

Default Value

None

Accepts

The names of one or more columns in a derived table

Definition

indexes lets you apply indexes to a persistent derived table’s columns. 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 the indexes parameter is missing from the query, Looker will warn you to add an indexes parameter to improve query performance. Learn more about indexing persistent derived tables on the Using Derived Tables documentation page.

If you are defining your derived table with the create_process parameter, you cannot use the indexes parameter. Instead, use a sql_step parameter to specify the SQL for the index. See the create_process documentation page for more information

If you use indexes with Redshift, you will create an interleaved sort key, which is overviewed in this blog post. You can also create regular sort keys using sortkeys, but you cannot use both at the same time. Distribution keys can be created with distribution.

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 a customer_order_facts persistent native derived table (NDT). The NDT should rebuild when the order_datagroup datagroup is triggered and have an index on customer_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 a customer_order_facts persistent derived table that is based on a SQL query and applies an index on customer_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 a customer_day_facts derived table with indexes on both customer_id and date:

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 a customer_day_facts derived table with an interleaved sort key built from customer_id and date:

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"] } }

Common Challenges

indexes Only Works with Derived Tables That Are Persisted

Derived tables can be calculated at query time, or they can be made persistent using datagroup_trigger, persist_for, or sql_trigger_value. The indexes parameter works only with persistent derived tables.

Not All Databases Support indexes

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

Top