Use ILIKE/LIKE + UNION in Project.search

This chance is broken up in two steps:

1. Use ILIKE on PostgreSQL and LIKE on MySQL, instead of using
   "WHERE lower(x) LIKE lower(y)" as ILIKE is significantly faster than
   using lower(). In many cases the use of lower() will force a slow
   sequence scan.

2. Instead of using 1 query that searches both projects and namespaces
   using a JOIN we're using 2 separate queries that are UNION'd
   together. Using a JOIN would force a slow sequence scan, using a
   UNION avoids this.

This method now uses Arel as Arel automatically uses ILIKE on PostgreSQL
and LIKE on MySQL, removing the need to handle this manually.

1 files changed, 24 insertions, 6 deletions

diff --git a/app/models/project.rb b/app/models/project.rb
index 65829bec77a..2f19ec9ba89 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -266,13 +266,31 @@ class Project < ActiveRecord::Base
       joins(:issues, :notes, :merge_requests).order('issues.created_at, notes.created_at, merge_requests.created_at DESC')
     end
 
+    # Searches for a list of projects based on the query given in `query`.
+    #
+    # On PostgreSQL this method uses "ILIKE" to perform a case-insensitive
+    # search. On MySQL a regular "LIKE" is used as it's already
+    # case-insensitive.
+    #
+    # query - The search query as a String.
     def search(query)
-      joins(:namespace).
-        where('LOWER(projects.name) LIKE :query OR
-              LOWER(projects.path) LIKE :query OR
-              LOWER(namespaces.name) LIKE :query OR
-              LOWER(projects.description) LIKE :query',
-              query: "%#{query.try(:downcase)}%")
+      ptable  = Project.arel_table
+      ntable  = Namespace.arel_table
+      pattern = "%#{query}%"
+
+      projects = select(:id).where(
+        ptable[:path].matches(pattern).
+          or(ptable[:name].matches(pattern)).
+          or(ptable[:description].matches(pattern))
+      )
+
+      namespaces = select(:id).
+        joins(:namespace).
+        where(ntable[:name].matches(pattern))
+
+      union = Gitlab::SQL::Union.new([projects, namespaces])
+
+      where("projects.id IN (#{union.to_sql})")
     end
 
     def search_by_visibility(level)