From: "James Drabb" <[EMAIL PROTECTED]> > From: Jigal van Hemert [mailto:[EMAIL PROTECTED] > 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 An exact phrase that forms the beginning of the varchar column is efficiently searched by the LIKE operator on an indexed column: ....WHERE col1 LIKE 'bla and other bla%'
Otherwise you can use a full text index and use MATCH(...) AGAINST(... IN BOOLEAN MODE) See: http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html > 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. Depending on how much data is added a time, you can perhaps run an extra app of yourself after the syslog ran to process the records which have been added. If it adds say 100 records a time and it gives you a performance boost of a factor 100 to have preprocessed data on which to search quickly through a few months worth of data, I'd say that this would be a solution to look at. > 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. A plain vanilla full text search sorts by relevance... > 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? Nope, there is no way (yet) to change the order of an index (it's somewhere in the todo list though). The ORDER BY MsgDate DESC does slow down the query. Sort in ascending order is faster than in descending order. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
