SEO

Hi all!

In mysql 4.0.24 on debian sarge (Hardware:Pentuim4 2.8MHz, RAM: 2GB),
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!
Well, like good friends you are, you will say, "index it!". Yes, I
will, but on the same server run several other services, so I cannot
permit the indexing take much more resources.

I searched google, mysql.org, googlegroups and others and read about
"delay insert", "--delay-key-write" and others good tools, but I didnt
answer myself a simple question, and I hope you can:
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.



Any hint is welcome.
Sorry my bad english.



Thank you
Tom Lobato

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

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/

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/

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/

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/

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/

Hi Tom,

Google seems to be seriously broken. You're the second guy with
5 copies of the same posting. Or did you send it 5 times?

"Tom" <tomlobato@gmail.com> wrote:
> Axel Schwenke escreveu:
>
>> 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.

OK. That was a mistake then.

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

Not only MATCH AGAINST. Every action on a table that can use an index
will use it. Having an index with different content than the table will
produce a lot of unpleasent results.

OK, that's a bad example since FULLTEXT indexes are special and not
being used internally.

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

You can't do such a thing.

I spoke hypothetically (to explain why it's not implemented that way).
If MySQL could do such a thing, it had to do each search twice: once
using the index and once not using the index, then combining the
results. But the same could be done by just ignoring the index and
scanning the datafile (the second part of the first approach).
The index would be completely useless.

Of course you can do something similar above database level: have a
readonly table with proper indexes and another one without indexes (but
fast INSERTs). Every once in a while you combine a new read only table
from both of them. However, all the logic (lookup both tables, combine
results) has to be done in your application. And still there is no easy
solution for DELETE and UPDATE.


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/

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.


Tom lobato

hi!

Axel Schwenke escreveu:
> Hi Tom,
>
> Google seems to be seriously broken. You're the second guy with
> 5 copies of the same posting. Or did you send it 5 times?

In really, google returned errors telling me it could not post, so I
posted more than on time, but now I see each time I tryed, It posted.

> "Tom" <tomlobato@gmail.com> wrote:
> > Axel Schwenke escreveu:
> >
> >> 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.
>
> OK. That was a mistake then.
>
> >> > 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.
>
> Not only MATCH AGAINST. Every action on a table that can use an index
> will use it. Having an index with different content than the table will
> produce a lot of unpleasent results.
>
> OK, that's a bad example since FULLTEXT indexes are special and not
> being used internally.
>
> > 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.
>
> You can't do such a thing.
>
> I spoke hypothetically (to explain why it's not implemented that way).
> If MySQL could do such a thing, it had to do each search twice: once
> using the index and once not using the index, then combining the
> results. But the same could be done by just ignoring the index and
> scanning the datafile (the second part of the first approach).
> The index would be completely useless.
>
> Of course you can do something similar above database level: have a
> readonly table with proper indexes and another one without indexes (but
> fast INSERTs). Every once in a while you combine a new read only table
> from both of them. However, all the logic (lookup both tables, combine
> results) has to be done in your application. And still there is no easy
> solution for DELETE and UPDATE.
>
>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB

well, I will digest it all =)
but I understood your point.

Thank you



Tom lobato