"Tom" <tomlobato@gmail.com> wrote:

> I have a table with 4GB. One of the 3 fields is of type text, and, of
> course, when I make
> select * from <table> where <textfield> like '%keyword%';
> I have to wait too much, sometimes 10 minutes!

This query is doing a full table scan. If it takes 10 minutes to scan
4GB, your disk is rather slow at 6.8MB/s.

> Well, like good friends you are, you will say, "index it!".

No. WHERE <column> LIKE '%keyword%' will never use an index. You may
want to have a look at MySQLs FULLTEXT indexing:

http://dev.mysql.com/doc/refman/5.0/...xt-search.html

> on the same server run several other services, so I cannot
> permit the indexing take much more resources.

Indexing is not *that* expensive. In fact creating the initial index
will take some time. But updating the index for INSERT/UPDATE/DELETE
operations is pretty fast.

> Can mysql only make inserts without indexing, and after (with cron,
> maybe at 3am) do the indexing of inserted rows? So I enjoy speed when
> select'ing with no performance drawbacks when insert'ing.

No. Also this would have some interesting consequences: if you search
the table by index but do not update the index for new records, such
records will not be found. OTOH DELETED records will still show up if
you don't update the index immediately.

A combined search (once with the index and once without it) will take
longer than not using an index at all.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/