MyBB Community Forums

MyBB 1.4.4, MySQL 5.0.45

Default syndication/RSS fetches threads from all forums. This produce a SQL query like:

SELECT t.tid, t.dateline, p.edittime, t.subject, f.allowhtml, f.allowmycode, f.allowsmilies, f.allowimgcode, f.name, p.message, u.username, p.smilieoff, f.fid
FROM mybb_threads t
LEFT JOIN mybb_forums f ON ( f.fid = t.fid ) 
LEFT JOIN mybb_posts p ON ( p.pid = t.firstpost ) 
LEFT JOIN mybb_users u ON ( u.uid = p.uid ) 
WHERE t.visible =1
AND t.closed NOT LIKE 'moved|%'
AND f.fid NOT 
IN (
'15', '14', '34', '27'
)
ORDER BY t.dateline DESC 
LIMIT 0 , 20

Query time: ~3 seconds
Explain says: rows=99500 (Using where; Using filesort)
Using filesort is actually using a temporary table which kills DB. I can see this queries in "SHOW PROCESSLIST" and they take sometimes from 2 to 15 seconds (depends on load). This query actually locks for read 4 tables for 3 seconds.


There is a way to improve it by a factor of 1000.
FIND in query:

WHERE t.visible =1

REPLACE:

WHERE (t.dateline >= " . TIME_NOW . " -864000)
AND t.visible =1

Query time: ~ 0.003 seconds
Explain says: rows=3304 (Using where)

This change:
- limits the number of rows to ~3000 which gives a major speedup
- triggers the use of 'dateline' index by MySQL which eliminates file sorting (MySQL can "order" rows by an index, if the index is used, which is really fast and does not create temporary table).

There is no need to fetch older threads (than for example 10 days) to RSS feed so this change gives a lot.

We already fixed this issue (and several others) for MyBB 1.4.5 a while back. The actual fix doesn't limit it, but instead breaks the query up into smaller, easier to parse chunks for MySQL.

OK, thanks for reply.