## TL;DR
A BigQuery search index makes `SEARCH()` queries over text and JSON fast by pre-tokenizing the data, the way a book index beats reading every page. Create it with `CREATE SEARCH INDEX` on the columns you search, then use `SEARCH(column, 'term')` instead of `LIKE '%term%'`. It pays off when you repeatedly search large log tables; for one-off greps it is overhead.

## The query

```text
bigquery search index for log analytics
```

## Use this when
- `LIKE '%error%'` over log tables is slow, expensive, or timing out
- You search tokenized text across JSON or semi-structured log fields regularly
- You are choosing between a search index, brute-force scans, and a dedicated log search tool

## Not for
- Exact equality or range filters, which partitioning and clustering handle better
- Complex regex extraction, search indexes are token-based, not pattern-based
- Small tables or rare searches, where a full scan is cheaper than maintaining an index

## Steps

1. Confirm the pain: check how much your current text search scans:

```sql
SELECT query, total_bytes_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND query LIKE '%SEARCH%error%'
ORDER BY total_bytes_processed DESC LIMIT 10;
```
Expected output: your heaviest text-search queries and their byte counts. If the same log table keeps showing up, it is a search-index candidate.

2. Create the search index on the columns you actually search. The index stores tokens, so pick text, JSON, or the specific JSON paths:

```sql
CREATE SEARCH INDEX logs_search_idx
ON `project.dataset.app_logs`(message, OPTIONS(json_scope = 'JSON_VALUES'));
```
Expected output: `CREATE SEARCH INDEX` succeeds. Index creation takes time proportional to table size; check `INFORMATION_SCHEMA.SEARCH_INDEXES` for build status before relying on it.

3. Rewrite the slow query to use SEARCH instead of LIKE:

```sql
-- before: full scan
SELECT * FROM `project.dataset.app_logs`
WHERE message LIKE '%payment failed%';

-- after: index-assisted
SELECT * FROM `project.dataset.app_logs`
WHERE SEARCH(message, 'payment failed');
```
Expected output: same logical rows, far fewer bytes scanned. SEARCH matches tokens, so word order and stemming behave differently than LIKE; verify result counts match on a sample before switching dashboards.

4. For JSON logs, scope the search to the fields that matter. Searching the whole document tokenizes everything including noise:

```sql
CREATE SEARCH INDEX logs_json_idx
ON `project.dataset.app_logs`(payload, OPTIONS(json_scope = 'JSON_VALUES'));
```
Expected output: an index over JSON values only, not keys. Combine with a filter like `WHERE SEARCH(payload, 'timeout') AND severity = 'ERROR'`; the structured filter still benefits from partitioning.

5. Check the index is being used and measure the win:

```sql
SELECT * FROM `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE table_name = 'app_logs';
```
Expected output: index status and size. Compare bytes scanned before and after on the same query; a healthy search index typically cuts text-search scans by an order of magnitude or more on large tables.

6. Know the maintenance cost: the index updates as data lands, which adds write cost and a small freshness lag. For streaming inserts the lag is usually seconds, fine for analytics, not for real-time alerting. If you need sub-second alerting on logs, that is a job for a dedicated log pipeline, not BigQuery.
Expected output: a conscious tradeoff, not a surprise bill. Drop indexes on tables nobody searches anymore; stale indexes are pure cost.

## Variant phrasings

### bigquery SEARCH function vs LIKE performance
SEARCH uses the search index and is token-based; LIKE is a full scan with substring matching. They are not interchangeable for exact substring needs, but for keyword search SEARCH wins by a lot.

### bigquery full text search on json column
Create the search index with json_scope JSON_VALUES and use SEARCH on the JSON column. For per-key search, extract the key with JSON_VALUE and index that path specifically.

### bigquery search index pricing
Index storage and maintenance cost extra on top of the table. Price it against the bytes your LIKE scans were burning; the index usually wins when searches are frequent.

## Why it matters
Log analytics in BigQuery dies on `LIKE '%...%'` because every search reads every byte. A search index is the standard fix, but teams either dont know it exists or create one and keep writing LIKE queries that ignore it. The rewrite in step 3 is the whole game.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_yGqGANI6h3bX1Sehxt5ZPg