Thanks for all your help guys,
Using COUNT(*) I get the following:
mysql> SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('blue
jeans');
+----------+
| COUNT(*) |
+----------+
| 51513 |
+----------+
1 row in set (48.58 sec)
The EXPLAIN for the fulltext queries look like this:
mysql> EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('blue jeans');
+------------------+----------+---------------+-----------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+------------------+----------+---------------+-----------+---------+------+------+-------------+
| product_fulltext | fulltext | search_ft | search_ft | 0 | | 1 |
Using where |
+------------------+----------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
Seems like the explain is working properly.
But even using COUNT and not pulling any data from the db, mysql still seems to lag
significantly depending on the number of fulltext matches.
mysql> SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('Oregon');
+----------+
| COUNT(*) |
+----------+
| 1876 |
+----------+
1 row in set (3.14 sec)
mysql> SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Brown');
+----------+
| COUNT(*) |
+----------+
| 18510 |
+----------+
1 row in set (21.19 sec)
mysql> SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Black')
LIMIT 10;
+----------+
| COUNT(*) |
+----------+
| 120309 |
+----------+
1 row in set (1 min 25.00 sec)
Ouch, that last one hurt.
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
