A mistake I made for a long time with MATCH AGAINST is to not include it in the WHERE
clause
eg I did
SELECT ID,MATCH Q,A AGAINST ("This") AS Score FROM faq
instead of
SELECT ID,MATCH Q,A AGAINST ("This") AS Score FROM faq WHERE MATCH Q,A AGAINST ("This")
The latter will give much better performance
----- Original Message -----
From: "Noor Dawod" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 7:51 AM
Subject: TEXT's are giving us headaches!
> Hello,
>
> We operate a forums website and use Phorum web application (PHP/MySQL
> backend), more info: http://www.phorum.org
>
> After operating for two years now, we are facing a serious problem.
> Giving the fact that the forums are full of content, ONE (1) search
> operation on a single forum is draining a "Xeon 933-Mhz 1GB RAM" of its
> power entirely! The MySQL daemon goes to 97% CPU usage, and all other
> queries are delayed until the search query finishes. This usually takes
> up from few tens of seconds to minutes until the search is over.
>
> The messages themselves that are added to the different forums are
> inserted into a TEXT field. We use MySQL 3.23.32, so I recently added a
> FULLTEXT index for this field on all forums and changed the source code
> to make use of the MATCH() AGAINST() MySQL call.
>
> This still doesn't help much, and the CPU usage after one search
> operation is unbearable.
>
> I need solutions, and I would like to get yours if you have any. The
> my.cnf file that we use is as follows:
>
> ----------------------------------------------
> [client]
> port = 3306
> socket = /mysql/mysql.socket
>
> [mysqld]
> port = 3306
> socket = /mysql/mysql.socket
> pid-file = /mysql/mysql.pid
> tmpdir = /mysql/tmp/
>
> set-variable = key_buffer=256M
> set-variable = max_allowed_packet=2M
> set-variable = table_cache=256
> set-variable = sort_buffer=64M
> set-variable = tmp_table_size=8M
> set-variable = record_buffer=4M
> set-variable = myisam_sort_buffer_size=32M
> set-variable = thread_cache=16
> set-variable = thread_concurrency=2 # Try number of CPU's*2
> set-variable = max_connections=256
>
> skip-locking
> skip-name-resolve
> log
> log-slow-queries
> ----------------------------------------------
>
> By the way, one of the solutions that were suggested is to turn into a
> commercial SQL solution, such as Oracle or DB2, and solve this problem
> for good, but I still like MySQL and its speed and simplicity.
>
> Thank you for your help.
>
> Noor
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
