MyBB Community Forums

My mysql-slow-queries.log is full of search queries from my largest mybb site.

How can I improve the performance? I tried to add default to search the past 3 months but it still checks the entire set of rows nearly 200,000 of them.

Wouldn't it be wise to first do a query for when the rows should start then commit the search query? I know that's 2 queries instead of one but imho it makes sense. My logs show 4-15 seconds for a search using the current method.

# Time: 080728 16:19:59
# User@Host: admin_xxxxxx[admin_xxxxxx] @ localhost []
# Query_time: 13  Lock_time: 0  Rows_sent: 2147  Rows_examined: 194097
SELECT p.pid, p.tid
                        FROM mybb_posts p
                        LEFT JOIN mybb_threads t ON (t.tid=p.tid)
                        WHERE 1=1      AND t.fid NOT IN ('21','56','22','23','55','57','58','59','60') AND p.visible>0 AND t.visible>0 AND t.closed NOT LIKE 'moved|%'  AND LOWER(p.message) LIKE '%msn%';

Anyways...can we discuss ways to improve search? It's one thing I am willing to make custom core edits to help improve site performance.

Thank you.

(2008-07-28, 08:29 PM)labrocca Wrote: [ -> ]How can I improve the performance? I tried to add default to search the past 3 months but it still checks the entire set of rows nearly 200,000 of them.

How are you doing that - adding an extra dateline condition to the query? Is the dateline field indexed?
Also, if you haven't already, try enabling MySQL Fulltext indexing.