As for average length, I don't think that's the issue. The system I'm designing searches resumes, so while there are only about 15k records, the "average" length is about 3 pages of text. My data size is actually about the same size as yours. My response time on a test machine is about 6-8 seconds, but the test machine is woefully underpowered for real use (Mac 350Mhz G4 256MB RAM).
I would definitely try increasing your buffer variables. Double them and see what happens. Many times on this list people have gotten significant performance boosts from setting high sort buffers. Please post results if it works.
On Aug 27, 2004, at 9:28 AM, [EMAIL PROTECTED] wrote:
Thanks Brent and Donny, hopefully this info will help get to the root of the problem with the fulltext search.
The table structure is very, very simple:
mysql> describe product_fulltext; +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | product_id | int(9) | | PRI | 0 | | | search_text | text | | MUL | | | +-------------+--------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
Space usage : Type Usage Data 502,455 KB Index 440,412 KB Total 942,867 KB
Row Statistic : Statements Value Format dynamic Rows 3,237,981 Row length ø 158 Row size ø 298 Bytes
MySQL 4.0.20-standard-log Official MySQL RPM
I also calculated the average text feild length:
mysql> SELECT AVG(LENGTH(search_text)) AS avg_length FROM product_fulltext;
+------------+
| avg_length |
+------------+
| 147.2239 |
+------------+
1 row in set (33.34 sec)
Is my average text length too long? Is MySQL 4.0.20 really that slow for fulltext searching? If so, how much will performance increase by upgrading to 4.1.x? Is upgrading difficult?
You may try doubling or tripling your sort_buffer and myisam_sort_buffer settings and maybe you read_buffer.
My sort_buffer is 10Mb, the read_buffer is 2Mb and the myisam_sort_buffer is 64Mb ... are these still too low?
I'm think I'm going to try to install "mytop" to get more performance info.
Currently, my temporary solution is to limit the query to 2000 rows but it still takes 4-5 seconds and doesn't give a complete picture for search results.
Thanks for any help on this, - 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]
