title: Improve BigQuery case insensitive search performance
body: <blockquote>
    <p><strong>The BigQuery team strikes again</strong>: This question is not longer relevant, as the results with LOWER() are as fast as with REGEX() now.</p>
  </blockquote>
  
  <hr>
  
  <p>Processing ~5GB of data with BigQuery should be super fast. For example the following query performs a case insensitive search in 18 seconds:</p>
  
  <pre><code>#standardSQL
  SELECT COUNT(*) c
  FROM `bigquery-public-data.hacker_news.full` 
  WHERE 
    LOWER(text) LIKE '%bigquery%' # 18s
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>#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
  </code></pre>
  
  <p>How can I improve my query performance?</p>