Professional Web Applications Themes

Delaying indexing - MySQL

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

  1. #1

    Default Delaying indexing

    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 Guest

  2. #2

    Default Re: Delaying indexing

    "Tom" <com> wrote:
     

    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.
     

    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/en/fulltext-search.html
     

    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.
     

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

  3. #3

    Default Re: Delaying indexing

    Hello Axel!
    Thank you for answer.


    Axel Schwenke escreveu:
     
    >
    > 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.[/ref]

    'hdparm -tT /dev/hda' gave me 34.91 MB/sec. I don't know why such
    slowness for mysql.
     
    >
    > No. WHERE <column> LIKE '%keyword%' will never use an index. You may
    > want to have a look at MySQLs FULLTEXT indexing:[/ref]

    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.
     
    >
    > 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.[/ref]

    ok, so I will test index it.
     
    >
    > 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.[/ref]

    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?
     

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

    Tom Guest

  4. #4

    Default Re: Delaying indexing


    Hello Axel!
    Thank you for answer.


    Axel Schwenke escreveu:
     
    >
    > 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.[/ref]

    'hdparm -tT /dev/hda' gave me 34.91 MB/sec. I don't know why such
    slowness for mysql.
     
    >
    > No. WHERE <column> LIKE '%keyword%' will never use an index. You may
    > want to have a look at MySQLs FULLTEXT indexing:[/ref]

    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.
     
    >
    > 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.[/ref]

    ok, so I will test index it.
     
    >
    > 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.[/ref]

    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?
     

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

    Tom Guest

  5. #5

    Default Re: Delaying indexing

    Hello Axel!
    Thank you for answer.


    Axel Schwenke escreveu:
     
    >
    > 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.[/ref]

    'hdparm -tT /dev/hda' gave me 34.91 MB/sec. I don't know why such
    slowness for mysql.
     
    >
    > No. WHERE <column> LIKE '%keyword%' will never use an index. You may
    > want to have a look at MySQLs FULLTEXT indexing:[/ref]

    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.
     
    >
    > 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.[/ref]

    ok, so I will test index it.
     
    >
    > 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.[/ref]

    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?
     

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

    Tom Guest

  6. #6

    Default Re: Delaying indexing


    Hello Axel!
    Thank you for answer.


    Axel Schwenke escreveu:
     
    >
    > 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.[/ref]

    'hdparm -tT /dev/hda' gave me 34.91 MB/sec. I don't know why such
    slowness for mysql.
     
    >
    > No. WHERE <column> LIKE '%keyword%' will never use an index. You may
    > want to have a look at MySQLs FULLTEXT indexing:[/ref]

    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.
     
    >
    > 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.[/ref]

    ok, so I will test index it.
     
    >
    > 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.[/ref]

    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?
     

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

    Tom Guest

  7. #7

    Default Re: Delaying indexing


    Hello Axel!
    Thank you for answer.


    Axel Schwenke escreveu:
     
    >
    > 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.[/ref]

    'hdparm -tT /dev/hda' gave me 34.91 MB/sec. I don't know why such
    slowness for mysql.
     
    >
    > No. WHERE <column> LIKE '%keyword%' will never use an index. You may
    > want to have a look at MySQLs FULLTEXT indexing:[/ref]

    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.
     
    >
    > 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.[/ref]

    ok, so I will test index it.
     
    >
    > 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.[/ref]

    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?
     

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

    Tom Guest

  8. #8

    Default Re: Delaying indexing

    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" <com> wrote: 
    >
    > 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.[/ref]

    OK. That was a mistake then.
     
    >>
    >> 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.[/ref]
    >
    > oh yes, as match/agaisnt looks for only in the index.[/ref]

    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.
     
    >
    > please, give a example of such combined search. I didn`t understand
    > well.[/ref]

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

  9. #9

    Default Re: Delaying indexing


    Hello Axel!
    Thank you for answer.


    Axel Schwenke escreveu:
     
    >
    > 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.[/ref]

    'hdparm -tT /dev/hda' gave me 34.91 MB/sec. I don't know why such
    slowness for mysql.
     
    >
    > No. WHERE <column> LIKE '%keyword%' will never use an index. You may
    > want to have a look at MySQLs FULLTEXT indexing:[/ref]

    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.
     
    >
    > 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.[/ref]

    ok, so I will test index it.
     
    >
    > 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.[/ref]

    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?
     

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


    Tom lobato

    Tom Guest

  10. #10

    Default Re: Delaying indexing

    hi!

    Axel Schwenke escreveu: 

    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.
     
    > >
    > > 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.[/ref]
    >
    > OK. That was a mistake then.

    > >
    > > oh yes, as match/agaisnt looks for only in the index.[/ref]
    >
    > 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.

    > >
    > > please, give a example of such combined search. I didn`t understand
    > > well.[/ref]
    >
    > 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[/ref]

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

    Thank you



    Tom lobato

    Tom Guest

Similar Threads

  1. delaying creationComplete ?
    By philax in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: April 22nd, 10:02 PM
  2. Delaying CFLOCATION
    By crowden in forum Macromedia ColdFusion
    Replies: 1
    Last Post: February 16th, 04:21 PM
  3. delaying code
    By geut in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: March 2nd, 10:17 AM
  4. Replies: 3
    Last Post: October 17th, 05:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139