Those sorts of SQL queries aren't very fast compared to a dedicated index/search system like ElasticSearch. Especially when millions of rows are concerned.
Think of search engines like ElasticSearch and Solr as being purpose built for "search" rather than ad hoc querying.
They offer more advanced searching features like faceting and synonyms, if your example had been "SELECT id FROM pages WHERE title LIKE '%dog'" you could set things up so that matches for 'dog', 'dogs', 'doggie', 'puppy', 'pup', 'canine', and 'mans best friend' all returned the same results.
While you are absolutely correct that this won't scale, and doesn't have a lot of advanced features like faceting and synonyms, it's still a useful technique. I use the SQL LIKE operator all the time, especially when I just need a simple way of searching my data.
If you under a million rows, LIKE is reasonably performant.
Think of search engines like ElasticSearch and Solr as being purpose built for "search" rather than ad hoc querying.
They offer more advanced searching features like faceting and synonyms, if your example had been "SELECT id FROM pages WHERE title LIKE '%dog'" you could set things up so that matches for 'dog', 'dogs', 'doggie', 'puppy', 'pup', 'canine', and 'mans best friend' all returned the same results.