An index wouldn’t speed up the query, because for textual columns indexes work by indexing N characters starting from left. When you do LIKE ‘%text%’ it can’t use the index because there can be a variable number of characters before text.
What you should be doing is not use a query like that at all. Instead you should use something like FTS (Full Text Search) that MySQL supports for MyISAM tables. It’s also pretty easy to make such indexing system yourself for non-MyISAM tables, you just need a separate index table where you store words and their relevant IDs in the actual table.
Update
Full text search available for InnoDB tables with MySQL 5.6+.