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:
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.