-----Original Message-----
From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 25, 2004 7:56 AM
To: [EMAIL PROTECTED]
Subject: Re: FULLTEXT and large database
> > Using Match() Against() showed the Kiwi_MsgText FULLTEXT index
getting
> > used.
>
> Correct. Full text indexes can only be used on MyISAM tables and will
not
> find (at least in the basic form) > > matches that are present in more
> than 50% of the records.
So is there any way to efficiently search a TEXT field of 5 million rows
for exact phrases? In this case the MsgText field contains a
descriptive
error message that the admins want to search for to help tune the
network
> > What can I do to speed this up? Should I do a regular INDEX? I used
> > the configuration setting from the example huge.cnf file.
>
> Sometimes it's better to do some preprocessing while storing the data.
> If you search for several predefined > keywords it might be wise to
check
> for these keywords when storing the data and use a SET or so to store
> which keywords were present. This will reduce a text search to a bit
> compare.
Unfortunately, the syslog application is a closed sourced/proprietary
app
that the admins purchased and I do not have access to the source code to
change things. The syslog app created the MySQL table with no indexes
so
I created the index to help in searching the data.
When I run a FULLTEXT search and view the process list of MySQL in
MySQL,
I see in the state column that MySQL is "sorting results" which appears
to
take up some time.
Here is a typical query:
Select
DATE_FORMAT(MsgDate, '%c/%d/%Y') AS 'MsgDate',
MsgTime, MsgPriority, MsgHostname, MsgText
From
syslogd
WHERE
MATCH (MsgText) AGAINST ('RADIUS')
Order By
MsgDate Desc
LIMIT 200
Here an admin wants to find logs about a RADIUS server. The index on
MsgDate
is in ascending order, I guess I could make it descending?
> Regards, Jigal.
Thanks for any help,
Jim Drabb
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-
He who receives an idea from me, receives instruction himself without
lessening mine; as he who lights his taper at mine, receives light
without
darkening me. -- Thomas Jefferson; 1813
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-
James Drabb JR
Senior Programmer Analyst
Darden Restaurants
Business Systems
JDrabb at Darden dot com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
