Hello Axel!
Thank you for answer.


Axel Schwenke escreveu:

> "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.

'hdparm -tT /dev/hda' gave me 34.91 MB/sec. I don't know why such
slowness for mysql.

>
> > 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:

maybe I was not so clear. I didn't say "WHERE <column> LIKE '%keyword%"
uses index, I know we have to use match/against (I made a big search
before post first mail here). The above command is what I'm using now,
the slow way, the no-index way. Sure, if I index it, I have to change
the command for using match and against.

>
> 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.

ok, so I will test index it.

>
> > 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.

oh yes, as match/agaisnt looks for only in the index.
But If I still run "where column like <pattern>" it will do the normal
search? Although slow, search all, no?

> 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.

please, give a example of such combined search. I didn`t understand
well.

>
>
> 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/