Speeding up point lookup queries with search optimization

Point lookup queries are queries that are expected to return a small number of rows. The search optimization service can improve the performance of point lookup queries that use:

  • Equality predicates (for example, column_name = constant).

  • Predicates that use IN (see example).

The following sections provide more information about search optimization support for point lookup queries:

Enabling search optimization for point lookup queries

Point lookup queries aren’t improved unless you enable search optimization for the columns referenced by the predicate of the query. To improve the performance of point lookup queries on a table, use the ALTER TABLE … ADD SEARCH OPTIMIZATION command to:

  • Enable search optimization for specific columns.

  • Enable search optimization for all columns of the table.

In general, enabling search optimization only for specific columns is the best practice. Use the ON EQUALITY clause to specify the columns. This example enables search optimization for a specific column:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(mycol);
Copy

To specify EQUALITY for all columns of the supported data types (except for semi-structured and GEOGRAPHY):

ALTER TABLE mytable ADD SEARCH OPTIMIZATION;
Copy

For more information, see Enabling and disabling search optimization.

Examples of supported point lookup queries

The search optimization service can improve the performance of the following query that uses an equality predicate:

SELECT * FROM test_table WHERE id = 3;
Copy

The IN clause is supported by the search optimization service:

SELECT id, c1, c2, c3
  FROM test_table
  WHERE id IN (2, 3)
  ORDER BY id;
Copy
Language: English