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
