Jesse
But then something else must be terribly wrong. As long as you are pulling
ten thousands of hits from the server, it may be slow. But when you reduce
the number of results with (let's say) 'LIMIT 100' I expect typical serch
times of 0.02 sec. That's what I see on a comparable machine holding 200
MB of text plus index.
Can you check for the response time on a not so common single word?
Thomas Spahni
(sql, query)
On Thu, 27 Feb 2003, Jesse Sheidlower wrote:
> On Thu, Feb 27, 2003 at 04:45:37PM +0100, Thomas Spahni wrote:
> > Jesse,
> >
> > this is slow because you are SEARCH'ing for 'COUNT(*)' on a word occurring
> > many many times.
> >
> > SELECT * FROM q WHERE MATCH(qt) AGAINST ('computer') LIMIT 100;
> >
> > should be fast. Make sure to use a key_buffer_size as big as you can
> > afford, possibly keeping the whole index in memory.
>
> This may be the answer for why it's _that_ slow for that one
> query, but in general I'm afraid that's not it. I executed
> your above query on my development server (to ensure the cache
> was cleared), which is a somewhat slower machine, and it took
> 2.61 seconds--better than 16, but still problematic.
>
> And when I changed this:
>
> > > mysql> SELECT COUNT(*) FROM q WHERE MATCH(qt)
> > > -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE);
> > > +----------+
> > > | COUNT(*) |
> > > +----------+
> > > | 44 |
> > > +----------+
> > > 1 row in set (1.71 sec)
>
> to this:
>
> mysql> SELECT * FROM q WHERE MATCH(qt)
> -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE);
>
> , it took 4.76 seconds--again, on a slower server, but this is
> returning only 44 results.
>
> It's certainly possible, and perhaps likely, that users will
> need to do fulltext searches on extremely common words--more
> common than "computer" in the above example--though limited by
> requirements in other tables not shown here, and it would be
> rather problematic if these searches are going to take over a
> second each.
>
> Jesse Sheidlower
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
