PDA

View Full Version : Query That causes boards to hang


sunygxc
03 Jul 2008, 21:43
There's a query that's being run from advanced search that's taking over 400 seconds to run and locks up the rest of the boards:

SELECT DISTINCT thread.threadid FROM thread AS thread INNER JOIN post AS post ON(thread.threadid = post.threadid AND post.userid IN(4626)) WHERE MATCH(post.title, post.pagetext) AGAINST ('\"cost of health care\"') AND thread.forumid NOT IN (0,31,4,15,18,14) AND post.visible = 1 LIMIT 500;

When I remove the AND post.userid statement, the query runs like a champ and completes in about 1 or 2 seconds. Any idea how I can disable this or come up with a work around?

I can reproduce this error everytime when I go to:
Advanced Search" -> search for a term in double quotes -> search for a specific user -> select all forums, excluding the moderators forum and the Message board guidelines forum.

Any help would be appreciated,

-Blair

noppid
03 Jul 2008, 21:48
Sounds like a table walk is occurring. Why is it pulling 500 records? That post.visible will include a huge return.

Have you optimized your tables? How many post and members do you have?

ssslippy
03 Jul 2008, 23:37
This sounds like someone is searching for all their posts.