MySQL is an odd database because it has pluggable engines that can work entirely differently. MyISAM is the base engine and of course InnoDB was a pluggable engine that became very popular.
Just about all DB's have some form of caching for queries. In the case of MyISAM this extends to the parsing of the queries themselves but not the actual data. With the buffer_pool in innodb -- the actual data retrieved is cached. So, in terms of what you were asking, when you do a LIKE '%...' query, the table will have to be scanned starting at row 1 and continuing to the last row in order to return a result. With Innodb and a large enough buffer pool, essentially the data will already be in memory and the tablescan process will be much faster than it would be reading the data from disk.
Unfortunately, with shared hosting, you are not in control of those parameters as you're sharing a mysql server your hosting company provides, either on your server or some secondary server they host.
In the shared host scenario, I would advise you to implement Barand's suggestion.
- Fluoresce likes this