Searching Large MySQL DB via CI - Best Way?

[eluser]dimensionmedia[/eluser]
Using CI to try to 'filter' out records from a database to dynamically show a few rows of content "live" on a site when certain keywords or tags are used. For example, I might be searching through 10,000 records in a single table in a particular field for tags:

apple,banana,strawberry,orange

I know I can do a SELECT statement with WHERE TAG LIKE %% with "OR" statements but is there any better way? Since these tags can change, I can't see how caching can be applied. So database pros, what's the most efficient way to do this?

As an extension, I would also like to search for tags across MULTIPLE tables. Same question. :-)

I'm not looking for straight CI code (although that would be nice), but general advise and/or some mysql statements would be great. Running PHP5 and latest Mysql on Mediatemple if that helps.