Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Improve BigQuery case insensitive search performance

The BigQuery team strikes again: This question is not longer relevant, as the results with LOWER() are as fast as with REGEX() now.


Processing ~5GB of data with BigQuery should be super fast. For example the following query performs a case insensitive search in 18 seconds:

#standardSQL
SELECT COUNT(*) c
FROM `bigquery-public-data.hacker_news.full` 
WHERE 
  LOWER(text) LIKE '%bigquery%' # 18s

Usually BigQuery is faster than this, but the real problem is that adding new search terms makes this query considerably slower (almost a minute with 3 search terms):

#standardSQL
SELECT COUNT(*) c
FROM `bigquery-public-data.hacker_news.full` 
WHERE 
  LOWER(text) LIKE '%bigquery%' OR LOWER(text) LIKE '%big query%' # 34s

#standardSQL
SELECT COUNT(*) c
FROM `bigquery-public-data.hacker_news.full` 
WHERE 
  LOWER(text) LIKE '%bigquery%' OR LOWER(text) LIKE '%big query%'
  OR LOWER(text) LIKE '%google cloud%' # 52s

How can I improve my query performance?

like image 489
Felipe Hoffa Avatar asked Jul 17 '26 09:07

Felipe Hoffa


1 Answers

Note from the team: Stay tuned! Very soon BigQuery will turn this advice irrelevant.

BigQuery performance tip: Avoid using LOWER() and UPPER()

LOWER() and UPPER() operations have a hard time when dealing with Unicode text: each character needs to be mapped individually and they can also be multi-bytes.

Solution 1: Case insensitive regex

A faster alternative: Use REGEX_MATCH() and add the case insensitive (?i) modifier to your regular expression

#standardSQL
SELECT COUNT(*) c
FROM `bigquery-public-data.hacker_news.full` 
WHERE 
   REGEXP_CONTAINS(text, '(?i)bigquery') # 7s

#  REGEXP_CONTAINS(text, '(?i)bigquery')
#   OR REGEXP_CONTAINS(text, '(?i)big query') # 9s

#  REGEXP_CONTAINS(text, '(?i)bigquery') 
#   OR REGEXP_CONTAINS(text, '(?i)big query') 
#   OR REGEXP_CONTAINS(text, '(?i)google cloud') # 11s

Performance is much better this way:

  • 1 search term: 18s down to 8s
  • 2 search terms: 34s down to 9s
  • 3 search terms: 52s down to 11s.

Solution 2: Combine regexes

Why do 3 searches when a regular expression can combine many into 1?

#standardSQL
SELECT COUNT(*) c
FROM `bigquery-public-data.hacker_news.full` 
WHERE 
  REGEXP_CONTAINS(text, '(?i)(bigquery|big query|google cloud)') # 7s

3 terms in 7s - nice.

Solution 3: Transform to bytes

This is uglier, but shows that UPPER() and LOWER() perform way better when dealing with individual bytes - for equivalent results in these searches:

#standardSQL
SELECT COUNT(*) c
FROM `bigquery-public-data.hacker_news.full` 
WHERE 
  LOWER(CAST(text AS BYTES)) LIKE b'%bigquery%'
  OR LOWER(CAST(text AS BYTES)) LIKE b'%big query%' 
  OR LOWER(CAST(text AS BYTES)) LIKE b'%google cloud%' # 7s

LOWER() is slower. Use the regex (?i) modifier instead.

If this worked for you, please feel free to comment with your performance improvements.

like image 188
Felipe Hoffa Avatar answered Jul 19 '26 07:07

Felipe Hoffa



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!