indexing a TEXT column

i have created an index on an ID column, how开发者_运维技巧ever i need to create some type of index or find a solution for when i am searching a TEXT column for a LIKE

its taking forever to execute the LIKE query on this text column because there are hundreds of thousands of rows.

i dont think i can just create an index on it, can i? what is the proper way to optimize this?

---

You might consider using a FULLTEXT index, if you're using MyISAM engine for your table, and you're willing to change the syntax a little bit. Check out this article for more info:

http://www.databasejournal.com/sqletc/article.php/1578331/Using-Fulltext-Indexes-in-MySQL---Part-1.htm

---

I would use Sphinx DB. It is much faster than MyISAM and allows for quick queries when doing 'like' type queries. Don't be overwhelmed by the documents on the site, it is quite easy to get up and running, and to create queries.

Essentially how it works is you do your fulltext query through sphinx, it returns you a list of primary keys which match your result, than you do a IN query (select * from table where id IN (list,of,ids);

Hopefully this helps, once I started using sphinx it was just night and day differences.

---

Also, you can use external search engines. Sphinx, mentioned by Frederico is the one of them. If you use PHP, look at Zend_Search_Lucene.