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.
Thomas Spahni
(sql, query)
On Thu, 27 Feb 2003, Jesse Sheidlower wrote:
> I'm having a problem with FULLTEXT searches going much more slowly
> than I expect, and need. It seems that this is perfectly straightforward
> so I can't see why it's taking so long; other people on this list have
> been reporting almost instantaneous results from FULLTEXT searches.
>
> I'm using MySQL 4.0.10 on FreeBSD 4.7, on a 1.4 GHz PIII with 1G RAM.
> It's a lightly loaded server most of the time.
>
> The table in question is:
>
> mysql> show create table q\G
> *************************** 1. row ***************************
> Table: q
> Create Table: CREATE TABLE `q` (
> `id` int(10) unsigned NOT NULL default '0',
> `cit_id` int(10) unsigned NOT NULL default '0',
> `qt` text,
> `note` text,
> PRIMARY KEY (`id`),
> KEY `cit_id` (`cit_id`),
> FULLTEXT KEY `qt` (`qt`)
> ) TYPE=MyISAM
> 1 row in set (0.00 sec)
>
> There are about 2.3M rows in this table, and it takes up about 400M.
> I did shorten the ft_min_word_length to 2, since I need to search on
> short words.
>
> Here's a sample:
>
> mysql> SELECT COUNT(*) FROM q WHERE MATCH(qt) AGAINST ('computer');
> +----------+
> | COUNT(*) |
> +----------+
> | 11892 |
> +----------+
> 1 row in set (16.43 sec)
>
> Boolean searches are also slow:
>
> mysql> SELECT COUNT(*) FROM q WHERE MATCH(qt)
> -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE);
> +----------+
> | COUNT(*) |
> +----------+
> | 44 |
> +----------+
> 1 row in set (1.71 sec)
>
> I don't get anything useful from EXPLAINs for searches like these:
>
> mysql> EXPLAIN SELECT COUNT(*) FROM q WHERE MATCH(qt)
> -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE)\G
> *************************** 1. row ***************************
> table: q
> type: fulltext
> possible_keys: qt
> key: qt
> key_len: 0
> ref:
> rows: 1
> Extra: Using where
> 1 row in set (0.00 sec)
>
> While a 1.7-second search may not be the end of the world, a 16-second
> search is getting closer to it, and this is just the simplest case. In
> practice, this would be an element of a larger search that's joining in
> a number of other tables, and with a number of concurrent users. Is there
> anything I can do to speed things up, or any explanation of why this is
> so slow?
>
> Thanks very much.
>
> 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
>
---------------------------------------------------------------------
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
