Let's say I have a single web page form user interface with 2 sets of checkboxes. With set 1 checkboxes, I can check off what Trainers I would like ("Jason", "Alexandra, etc.) With set 2 checkboxes, I can check off what animals I would like to see ("Tigers", "Bears", etc.) Once I submit the form with these options, I get back a list of zoos that match the criteria (let's assume all the trainers work at all the zoos and all the animals are at all the zoos for discussion's sake)
We'll be running our database query by "name" (e.g., search using trainer names and animal names, NOT database ids)
Let's say we are using a Postgres database that has hundreds of thousands of rows (if not millions).
Zoo.includes(:animals, :trainers).where("animals.name = ? and trainers.name = ?", animal_names, trainer_names)
class Zoo < ActiveRecord::Base
has_many :animals, through: zoo_animals
has_many :trainers, through: zoo_trainers
class Animal < ActiveRecord::Base
has_many :zoos, through :zoo_animals
class Trainer < ActiveRecord::Base
has_many :zoos, through :zoo_trainers
class ZooAnimal < ActiveRecord::Base
class ZooTrainer < ActiveRecord::Base
LIKE '%Jason%' is much less efficient than querying for the exact string 'Jason' (or querying for an ID), because while exact comparisons and some uses of
LIKE can use an index on the column being queried,
LIKE with a pattern beginning with a wildcard can't use an index.
However, performance doesn't sound like the most important consideration here.
LIKE %Jason% will still probably be fast enough on a reasonably sized database under reasonable load. If the application really needs to search for things by substring (which implies that a search might have multiple results), that requirement can't be met by simple equality.
There are an endless number of higher-powered solutions to searching text, including Postgres built-in full-text search and external solutions like Elasticsearch. Without specific requirements for scaling I'd go with
LIKE until it started to slow down and only then invest in something more complicated.