I have to drop the FULLTEXT INDEX added to a set of dbs containing a TEXT field with an average of 6K of text added to each row, at a growth of 250K rows added a day to a set of 100 tables. The reason for the drop is due to table corruption and non-related query slowdown. DELETES and Frequent UPDATES of a FULLTEXT INDEX column, IMO are the root cause of table corruption and query slow down time. The reason for this opinion is based on the situation where after adding the FULL TEXT INDEX to the body of each user's message, table corruption occurred everyday, and query time of non related queries to a full text column multiplied by 10 or more. The application made frequents updates to a table and a batch job or a user action would cause deletes. Here are the corruption errors. Can't open file: 'messages_06.MYI'. (errno: 145)
The fix REPAIR TABLE messages_06; I would see dead lock queries as well: Explanation below. Prior to the FULL TEXT INDEX queries normally took no more then a second. These same queries took nearly 5 min before I killed them. The stuck state was either waiting for table (btw no alter/flush change was going on) or Locked, but not locked on any particular query. The second granularity that "show full processlist" displays did not indicate which query was the problem. If mysql.com is interested in this issue, I can send a copy the table format etc. In the meantime I have to drop the FULL TEXT INDEX. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
