MySQL Forums
Forum List  »  Full-Text Search

How well does full-text scale?
Posted by: Andrey Osenenko
Date: October 04, 2009 12:58AM

Hello.

I have a mysql table; It's pretty large but straightforward. It has 12767901 entries; here's output of show create (irrelevant parts omitted):
CREATE TABLE `a` (
  `num` int(10) unsigned NOT NULL,
  `subnum` int(10) unsigned NOT NULL,
  `comment` text,

  ...

  PRIMARY KEY (`num`,`subnum`),
  FULLTEXT KEY `comment` (`comment`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

I want to use fulltext search on it, but it's ridiculously slow:
mysql> select count(*) from a where match(comment) against('pretty');
+----------+
| count(*) |
+----------+
|   176203 |
+----------+
1 row in set (13 min 23.29 sec)

mysql> select count(*) from a where match(comment) against('pretty');
+----------+
| count(*) |
+----------+
|   176203 |
+----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from a where match(comment) against('pretty');
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type     | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | fulltext | comment       | comment | 0       |      |    1 | Using where |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
1 row in set (0.12 sec)

Notice how the second time the same query completes immediately. Is this intended behavior or am I doing something wrong? show processlist says that query spends most time in Sending data state.

Options: ReplyQuote


Subject
Views
Written By
Posted
How well does full-text scale?
4334
October 04, 2009 12:58AM
2489
October 23, 2009 12:51AM
2599
November 29, 2009 04:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.