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.