Database hangs

[dai-kun]

Do you think you can integrate dynamics to optionally use sphinx as the search engine? I have so many posts within this addon that I believe is causing my database to hang (copying to tmp table) when someone is trying to search.

This query I believe is a search query:

SELECT entries.entryid
FROM vb3_adv_dyna_entries_pages AS entries_pages
LEFT JOIN vb3_adv_dyna_entries AS entries ON (entries_pages.entryid = entries.entryid)
LEFT JOIN vb3_adv_dyna_posts AS posts ON (posts.entryid = entries.entryid)
LEFT JOIN vb3_adv_dyna_cfields_entries AS customfields ON (entries.entryid = customfields.eid)
WHERE draft = 0
AND ( MATCH(entries.title) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR MATCH(entries_pages.pagetext) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR MATCH(entries.keywords) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR MATCH(posts.pagetext) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR field12 LIKE '%ao%' AND field12 LIKE '%no%' AND field12 LIKE '%exorcist%') AND (posts.visible IS NULL OR posts.visible = 1) AND entries.valid = 1
ORDER BY entries.dateline ASC LIMIT 500;

Everytime my server becomes unresponsive, I do a mysql processlist and it shows me a query similar to that. It last a very long time, sometimes up to 30 mins, which locks up my database and no one can do anything until that query dies.

I used to think it was something else in dynamics that caused my queries to hang, but I think this might finally be it.

[dai-kun]

Searching my dynamics takes at least 11 seconds for any results to show up normally. I am not sure how exactly the queries hang sometimes, causing the entire dynamics to become unresponsive. Then leads to hanging of the entire database.

I have a lot of fields, would that contribute to the long search? If we can't get sphinx to integrate, can we somehow speed up these slow queries? My members are not used to this since the forum post search only takes .5 secs to generate, and they are giving 11 secs to search dynamics.

[dai-kun]

Ok, I found the culprit for making my database hang. If a user is searching and tick the "posts" as part of the additional options, the query will run forever until the database crashes. I have disabled it for now. I think because of the quality (some posts have over 10,000 characters) and quantity (over 300,000 posts) that's causing the query to hang.

Is there a solution?