This seemed like the most appropriate place to post this.

I'm setting up a site using FB. Migrating from phpbb with about 650,000 total records (most of it in Fireboard). J1.0.13 CB 1.1 and FB 1.0.3

Doing some testing with several users we came across performance issues specifically with Fireboard. Our server admin setup Slow Query Logging in mysql. With just a couple of testers active we got over 20 slow queries. All except one were FB. Below is a record. He suggested to pass this onto the FB developers to see if they can be modified or otherwise remove the need for them. He says they are repeated over and over and are apparently the key issue downgrading performance.

Anything to be done for this as a quick fix?

[code:1]# Time: 071025 16:31:27
# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 16:31:40

# Query_time: 4 Lock_time: 0 Rows_sent: 20 Rows_examined: 86482
SELECT a. * , MAX( b.time ) AS lastpost FROM jos_fb_messages AS a LEFT JOIN jos_fb_messages AS b ON b.thread = a.thread WHERE a.parent = '0' AND a.catid = 1 AND a.hold = '0' GROUP BY id ORDER BY ordering DESC , lastpost DESC LIMIT 0,20;
# Time: 071025 16:32:01

# Query_time: 4 Lock_time: 0 Rows_sent: 20 Rows_examined: 86482
SELECT a. * , MAX( b.time ) AS lastpost FROM jos_fb_messages AS a LEFT JOIN jos_fb_messages AS b ON b.thread = a.thread WHERE a.parent = '0' AND a.catid = 1 AND a.hold = '0' GROUP BY id ORDER BY ordering DESC , lastpost DESC LIMIT 0,20;
# Time: 071025 16:33:13

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 16:35:30

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
/usr/libexec/mysqld, Version: 5.0.22-log. started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 071025 16:36:17

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 16:36:41

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 16:37:06

# Query_time: 4 Lock_time: 0 Rows_sent: 20 Rows_examined: 86482
SELECT a. * , MAX( b.time ) AS lastpost FROM jos_fb_messages AS a LEFT JOIN jos_fb_messages AS b ON b.thread = a.thread WHERE a.parent = '0' AND a.catid = 1 AND a.hold = '0' GROUP BY id ORDER BY ordering DESC , lastpost DESC LIMIT 0,20;
# Time: 071025 16:37:55
# User@Host: root[root] @ localhost []
# Query_time: 9 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 16:47:50

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 17:07:00

# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 681635
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 17:07:36

# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 681639
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 17:19:06

# Query_time: 4 Lock_time: 0 Rows_sent: 20 Rows_examined: 86484
SELECT a. * , MAX( b.time ) AS lastpost FROM jos_fb_messages AS a LEFT JOIN jos_fb_messages AS b ON b.thread = a.thread WHERE a.parent = '0' AND a.catid = 1 AND a.hold = '0' GROUP BY id ORDER BY ordering DESC , lastpost DESC LIMIT 0,20;
# Time: 071025 17:20:33

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681639
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 17:20:37

# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 318052
SELECT m.*, m.time AS sendtime FROM jos_fb_messages AS m
LEFT JOIN jos_fb_categories AS c ON c.id = m.catid
LEFT JOIN jos_users AS u ON u.gid >= c.pub_access
WHERE m.moved='0' AND u.id = 340 AND c.published = 1 AND m.hold = 0
ORDER BY m.id DESC LIMIT 0,1;
# Time: 071025 17:23:23

# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 681639
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 17:24:02

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 681641
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 17:24:13

# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 681641
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# Time: 071025 17:25:04

# Query_time: 4 Lock_time: 0 Rows_sent: 20 Rows_examined: 86485
SELECT a. * , MAX( b.time ) AS lastpost FROM jos_fb_messages AS a LEFT JOIN jos_fb_messages AS b ON b.thread = a.thread WHERE a.parent = '0' AND a.catid = 1 AND a.hold = '0' GROUP BY id ORDER BY ordering DESC , lastpost DESC LIMIT 0,20;
# Time: 071025 17:55:38

# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 681641
SELECT u.id, ifnull(u.username, 'Guest') as username, ifnull(u.name,'Guest') as name, fb.subject, fb.id as fbid, fb.catid, from_unixtime(fb.time) as date, fb.hits, fb.locked,fb.topic_emoticon, fb.parent, sc.name as catname FROM jos_fb_messages AS fb LEFT JOIN jos_users AS u ON u.id = fb.userid INNER JOIN jos_fb_categories sc on sc.id = fb.catid WHERE 1=1 AND (sc.pub_access
# date
Thu Oct 25 18:04:45 EDT 2007[/code:1]

Also, these recommendations for improving the FB mysql found here:
www.ircmaxell.com/articles/joomla/improv...joomlas-queries.html

I hope these will be useful to the development effort. But I'd like to know since this article was written in June before the 1.0.3 release would there be any potential problems with making these db optimizations?
We did the optimizations but didn't see any noticeable improvement. The comments on that link however have some reporting improvements.

[code:1]
ALTER TABLE `jos_fb_whoisonline` ADD INDEX `userid` (`userid`, `userip`, `time`, `what`);
ALTER TABLE `jos_fb_whoisonline` ADD INDEX `user` (`user`);
ALTER TABLE `jos_fb_sessions` ADD INDEX `userid` (`userid`);
ALTER TABLE `jos_fb_users` ADD INDEX `userid` (`userid`);
ALTER TABLE `jos_fb_users` ADD INDEX `post_userid` (`posts`, `userid`);
ALTER TABLE `jos_fb_messages` ADD INDEX `id_hold` (`id`, `hold`);
ALTER TABLE `jos_fb_messages` ADD INDEX `thread_id` (`thread`, `id`, `hold`, `catid`, `time`);
ALTER TABLE `jos_fb_messages` ADD INDEX `cat_parent_hold` (`catid`, `parent`, `hold`, `id`);
ALTER TABLE `jos_fb_messages` ADD INDEX `cat_hold_`(`catid`, `hold`, `moved`, `time`);
ALTER TABLE `jos_fb_messages_text` ADD INDEX `mesid` (`mesid`);
ALTER TABLE `jos_fb_moderation` ADD INDEX `cat_user` (`catid`, `userid`);
ALTER TABLE `jos_fb_categories` ADD INDEX `pub_name_par` (`published`,`name`(20),`parent`);
ALTER TABLE `jos_fb_categories` ADD INDEX `pub_id` (`pub_access`, `id`, `name`(20));[/code:1]

---------