Some fulltext queries hang miserably
Greetings,
I've been working on a site search that uses fulltext indices as its primary mode of locating things. In early testing, however, I noticed that certain queries would hang up royally (a bad thing since this would cause a rather busy table to be locked to inserts/updates). I initially decided to just mirror some of the fields necessary for search in another table to keep hangup problems isolated to that table. I had also hoped that moving the search entirely to a less active table would avoid these hangup problems. Not so. In fact, I can now always duplicate the problem with certain queries (that didn't always hang up before).
Specifically, the queries always hang up in the "Sending data" stage as reported by SHOW PROCESSLIST. This isn't a problem isolated to the exact hook I'm using since I can duplicate the hangup with both Apache's mod_php as well as the mysql CLI. The following query hangs up (for at least a few MINUTES; I've let it run that long) regardless of the LIMIT clause I set:
(SELECT id AS tid, NULL AS pid, poster, posted, ROUND(MATCH(subject) AGAINST('son'), 2) * 1.3 AS score
FROM topics_search
WHERE MATCH(subject) AGAINST('son' WITH QUERY EXPANSION) HAVING score > 0.2)
UNION
(SELECT p.topic_id AS tid, p.id AS pid, p.poster, p.posted, ROUND(MATCH(message) AGAINST('son'), 2) AS score
FROM posts_search AS p
WHERE MATCH(message) AGAINST('son' WITH QUERY EXPANSION) HAVING score > 0.2)
ORDER BY score DESC LIMIT 10;
Recall that this hangs in the "Sending data" phase pretty quickly regardless of what LIMIT is set to (usually it's something more like 100). Also recall that at one point this query worked relatively quickly - 5 or 6 seconds (mostly before I changed the fulltext search to use the duplicate tables). Messing around a bit seems to suggest that QUERY EXPANSION could be the culprit, but the very sporatic nature of this casts some doubt. I've seen a certain query hang up, kill it, run it again later, and it will complete in a few seconds.
I'm basically looking for any and all explanations of this erratic behavior. I'm using MySQL 4.1.14 currently.
Thanks for any assistance!
Edit: It's probably also worth noting that I rebuilt the FULLTEXT indices, so there shouldn't be any problem with them
Edited 1 time(s). Last edit at 04/26/2006 08:28PM by Robot Machine.