-----Original Message-----
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 25, 2004 9:50 AM
To: James Drabb
Cc: Jigal van Hemert; [EMAIL PROTECTED]
Subject: Re: FULLTEXT and large database
> You may try increasing your sort_buffer_size and key_buffer_size,
> see if that speeds up the searching and sorting.
I increased key_buffer_size to 768M and sort_buffer_size to 256M
and it seems to have helped. For example this query on
3.1 million rows returned in 0.02 seconds:
Select DATE_FORMAT(MsgDate, '%c/%d/%Y') AS 'MsgDate', MsgTime,
MsgPriority, MsgHostname, MsgText
>From syslogd
WHERE MATCH (MsgText) AGAINST ('LINK-CLUSTER_MEMBER_1-3-UPDOWN')
LIMIT 200
However, I have noticed something weird with a FULLTEXT search. This
query:
Select DATE_FORMAT(MsgDate, '%c/%d/%Y') AS 'MsgDate', MsgTime,
MsgPriority, MsgHostname, MsgText
>From syslogd
WHERE MsgPriority = 'Local7.Notice'
Order By MsgDate Desc LIMIT 200
Returns several rows and in the MsgText field (which has the FULLTEXT
index) for
one of the rows is this text:
12822: May 25 09:14:10.400 EST: %LINEPROTO-CLUSTER_MEMBER_1-5-UPDOWN:
Line protocol on Interface FastEthernet0/11, changed state to down
Now if I run this query to search for something in that field, I get no
records:
Select DATE_FORMAT(MsgDate, '%c/%d/%Y') AS 'MsgDate', MsgTime,
MsgPriority, MsgHostname, MsgText
>From syslogd
WHERE MATCH (MsgText) AGAINST ('May')
Order By MsgDate Desc LIMIT 200
The text 'May' is in the column, yet I get no rows returned? The admins
are looking
to be able to search for an arbitrary string in the MsgText field that
can be anywhere
in the field not just at the beginning. So it seems that a regular
index will not work,
yet a FULLTEXT index doesn't seem to be getting all the needed data.
Thanks for the tip,
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]
