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
