For full text searches, you definitely want to use match/against. If you are only interested in one months worth of data, are you deleting "old" records on a regular basis? You may want to run an optimize table or myisamchk after you do your add/delete. Your table may be getting severely fragmented since it contains variable length records. If you can post your query and how long it takes (along with explain), that would be helpful.
On May 25, 2004, at 8:40 AM, James Drabb wrote:
Hey group,
I am in need of some suggestions. I am a senior programmer for a fortune 500 where we mostly use Oracle and MS SQL Server. However, a project came along and no one wanted to spend more money on Oracle or SQL Server licenses so I suggested MySQL and to my surprise they let me set it up. All of our Unix and Linux servers are used to the max, so I was only given a 2-way 1.26 GHz PIII, 1.2GB, 136GB SCSI RAID 5 server running Windows 20003.
The MySQL database is being used for a proprietary win/linux/unix syslog
program that was purchased. Currently there are about 45,000 records
per
hour going in. The admin I set it up for would like to have one months
worth
of data. How big of a DB can MySQL handle? After two days of running
there
are 2,160,000 or so records in the database.
The table layout is:
CREATE TABLE `syslogd` ( `MsgDate` date default NULL, `MsgTime` time default NULL, `MsgPriority` varchar(30) default NULL, `MsgHostname` varchar(255) default NULL, `MsgText` text, KEY `kiwi_MsgDate` (`MsgDate`), KEY `Kiwi_MsgPriority` (`MsgPriority`), KEY `kiwi_MsgTime` (`MsgTime`), KEY `Kiwi_MsgHostname` (`MsgHostname`), FULLTEXT KEY `Kiwi_MsgText` (`MsgText`) ) TYPE=MyISAM;
I have a web app to allow admins to search this table. Any searches by
MsgDate, MsgTime, MsgPriority or MsgHostname or any combination of them
runs
pretty fast. When I try to do a search against MsgText, it takes quite
a
while. I originally tried INSTR(), however Explain showed that no INDEX
was
being used. Using Match() Against() showed the Kiwi_MsgText FULLTEXT
index
getting used.
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.
Thanks for any guidance,
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]
-- 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]
