indexes

Usage

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

- or -

explore
aggregate_table
materialization
indexes
Default Value
None

Accepts
The names of one or more columns in a PDT or an aggregate table

Special Rules
indexes is supported only on specific dialects

Definition

The indexes parameter lets you apply indexes to the columns of a persistent derived table (PDT) or an aggregate 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 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 Derived tables in Looker documentation page.

See the Dialect support for indexes section on this page for the list of dialects that support indexes.

The indexes parameter works only with tables that are persistent, such as PDTs and aggregate tables. indexes is not supported for derived tables without a persistence strategy.

In addition, the indexes parameter is not supported for derived tables that are defined using create_process or sql_create.

If you use indexes with Redshift, you will create an interleaved sort key. 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 derived table. The PDT should rebuild when the order_datagroup datagroup is triggered and will 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"]
  }
}

Dialect support for indexes

The ability to use indexes 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 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.

In the latest release of Looker, the following dialects support indexes:

Dialect Supported?
Actian Avalanche
Yes
Amazon Athena
No
Amazon Aurora MySQL
Yes
Amazon Redshift
Yes
Apache Druid
No
Apache Druid 0.13+
No
Apache Druid 0.18+
No
Apache Hive 2.3+
Yes
Apache Hive 3.1.2+
Yes
Apache Spark 3+
Yes
ClickHouse
No
Cloudera Impala 3.1+
No
Cloudera Impala 3.1+ with Native Driver
No
Cloudera Impala with Native Driver
No
DataVirtuality
No
Databricks
No
Denodo 7
No
Denodo 8
No
Dremio
No
Dremio 11+
No
Exasol
No
Firebolt
Yes
Google BigQuery Legacy SQL
No
Google BigQuery Standard SQL
No
Google Cloud PostgreSQL
Yes
Google Cloud SQL
No
Google Spanner
No
Greenplum
Yes
HyperSQL
No
IBM Netezza
No
MariaDB
Yes
Microsoft Azure PostgreSQL
Yes
Microsoft Azure SQL Database
Yes
Microsoft Azure Synapse Analytics
Yes
Microsoft SQL Server 2008+
Yes
Microsoft SQL Server 2012+
Yes
Microsoft SQL Server 2016
Yes
Microsoft SQL Server 2017+
Yes
MongoBI
Yes
MySQL
Yes
MySQL 8.0.12+
Yes
Oracle
Yes
Oracle ADWC
No
PostgreSQL 9.5+
Yes
PostgreSQL pre-9.5
Yes
PrestoDB
No
PrestoSQL
No
SAP HANA
Yes
SAP HANA 2+
Yes
SingleStore
Yes
SingleStore 7+
Yes
Snowflake
No
Teradata
Yes
Trino
No
Vector
Yes
Vertica
No