| Bug #57354 | Using "=" operator with FULL-TEXT search decrease the speed | ||
|---|---|---|---|
| Submitted: | 10 Oct 2010 19:52 | Modified: | 26 Dec 2010 17:00 |
| Reporter: | Simeon Ivanov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S5 (Performance) |
| Version: | 5.1.41, 5.1.55 | OS: | Any (CentOS and Win7 tested) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | boolean mode, full-text, speed | ||
[11 Oct 2010 3:14]
Valeriy Kravchuk
Please, send the results of: show create table `table`\G
[11 Oct 2010 17:14]
Simeon Ivanov
CREATE TABLE `offers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `body` text NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `body` (`body`,`title`) ) ENGINE=MyISAM AUTO_INCREMENT=43972 DEFAULT CHARSET=utf8 PS - I made some tests with my old table without fulltext indexes and after add it with: ALTER TABLE offers ADD FULLTEXT(body, title); the problem exist again.
[11 Nov 2010 11:19]
Simeon Ivanov
Is there any info about this issue? I mean did someone reproduce the defect successfully. :) Thanks!
[26 Dec 2010 17:00]
Valeriy Kravchuk
Verified with current mysql-5.1 from bzr on Mac OS X:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.55-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `offers` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `title` varchar(255) NOT NULL,
-> `body` text NOT NULL,
-> PRIMARY KEY (`id`),
-> FULLTEXT KEY `body` (`body`,`title`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into offers (title, body) values ('word1', 'word2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into offers (title, body) select concat('word', rand()*1000), concat('word', rand()*1000) from offers;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into offers (title, body) select concat('word', rand()*1000), concat('word', rand()*1000) from offers;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
...
mysql> insert into offers (title, body) select concat('word', rand()*1000), concat('word', rand()*1000) from offers;
Query OK, 32768 rows affected (7.66 sec)
Records: 32768 Duplicates: 0 Warnings: 0
mysql> select * from offers limit 4;
+----+----------------------+----------------------+
| id | title | body |
+----+----------------------+----------------------+
| 1 | word1 | word2 |
| 2 | word919.813753962343 | word800.5516620358 |
| 3 | word243.317079025616 | word814.930439754324 |
| 4 | word344.700992428419 | word278.713673612768 |
+----+----------------------+----------------------+
4 rows in set (0.00 sec)
mysql> SELECT `id` FROM `offers` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) = 2;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.41 sec)
mysql> SELECT `id` FROM `offers` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) = 2;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.41 sec)
mysql> SELECT `id` FROM `offers` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) >= 2;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.02 sec)
mysql> SELECT `id` FROM `offers` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) >= 2 AND
-> MATCH(`title`, `body`)
-> AGAINST ('word1 word2 word3' IN BOOLEAN MODE) < 3
-> ;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.02 sec)
Looks like index is not used with =2:
mysql> explain SELECT `id` FROM `offers` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) = 2;
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | offers | ALL | NULL | NULL | NULL | NULL | 65536 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT `id` FROM `offers` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) >= 2 AND MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) < 3;
+----+-------------+--------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+----------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | offers | fulltext | body | body | 0 | | 1 | Using where |
+----+-------------+--------+----------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Description: Using "=" operator instead of ">=" or "<=" decrease the speed a lot. How to repeat: To reproduce the speed problem is better to test on table with many records. In my case, I have about 50,000 rows. This query took 2.0154 sec: SELECT `id` FROM `table` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) = 2 But this one, only 0.0012 sec: SELECT `id` FROM `table` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) >= 2 If I need the first situation (with only "="), but to be faster, this solution takes acceptable 0.0324 sec: SELECT `id` FROM `table` WHERE MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) >= 2 AND MATCH(`title`, `body`) AGAINST ('word1 word2 word3' IN BOOLEAN MODE) < 3 The tests are made with "FLUSH TABLE `table`;" before query and I see that if NOT flush the table, first query (with only "=") is not cached and took the same time.