Hello there,
We got a problem atm with our 20k database. Everytime we search for sth. with double quotes it takes much more longer than without.
Here is what we did:
mysql> SELECT COUNT(*) FROM `Pages` WHERE MATCH (`Text`) AGAINST ('"term"' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 3572 |
+----------+
1 row in set (6.58 sec)
mysql> SELECT COUNT(*) FROM `Pages` WHERE MATCH (`Text`) AGAINST ('term' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 3572 |
+----------+
1 row in set (0.13 sec)
CREATE TABLE `Pages` (
`ID` int(11) NOT NULL default '0',
`Title` varchar(255) NOT NULL default '',
`Text` mediumtext NOT NULL,
PRIMARY KEY (`ID`),
FULLTEXT KEY `Text` (`Text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
mysql> EXPLAIN SELECT COUNT(*) FROM `Pages` WHERE MATCH (`Text`) AGAINST ('term' IN BOOLEAN MODE);
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | Pages | fulltext | Text | Text | 0 | | 1 | Using where |
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM `Pages` WHERE MATCH (`Text`) AGAINST ('"term"' IN BOOLEAN MODE);
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | Pages | fulltext | Text | Text | 0 | | 1 | Using where |
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
We know that in that case we didn't need to search with quotes; this e.g. has been used for comparison reasons.
If some of you got any idea how to fasten up the search please let us know!
With best regards,
Malte Fiala
