If you are running Unix, try running: vm_stat 1
That will show you memory stats every second. Watch the pagein/pageout columns when you have a query running. Ideally, they should be zero. Otherwise you are basically using your hard drive as RAM, which would be the cause of your slow down.
On Aug 12, 2004, at 12:53 PM, [EMAIL PROTECTED] wrote:
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]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
