>From what someone on this (can't remember who or I would give credit) list told me,
>It ONLY uses the index if you have it in the WHE
RE clause... and apparently the SQL optimizer catches that the MATCH AGAINST is the
same in both places and only runs it once...
----- Original Message -----
From: "Noor Dawod" <[EMAIL PROTECTED]>
To: "Jason Terry" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 9:23 AM
Subject: RE: TEXT's are giving us headaches!
> Hi
>
> Hmm, this seems to me like redundant code.. if you're holding info in
> Score, and not using it but only using the same operation that was used
> to create it, then it's a redundant code..
>
> Are you sure the second form works faster than the first one?
>
> Noor
>
> -----Original Message-----
> From: Jason Terry [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 13, 2001 6:07 PM
> To: Noor Dawod
> Cc: [EMAIL PROTECTED]
> Subject: Re: TEXT's are giving us headaches!
>
>
> 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
