Dear FireBoard users,
In order to further develope the need of Joomla for a robust forum, FireBoard component has been moved to its own new home and new name. You can find more information regarding the Kunena, the successor of FireBoard, at kunena.com. The current forum here will stay for some time more for referencing needs. You can find the FireBoard files under joomlacode.org and other files
Upgrading to Kunena is highly recommended!

Kunena! - To Speak!

A revolution is coming... Kunena - Swahili for: To Speak! THE Next generation of native Joomla based Forum ultra component. Forget what you know about Joomla based forums...
Amazingly Simple Lean and Clean Easier Customization
Kunena is about to redefine the world of Joomla! with the most simplistic ultra component out there. For ultimate performance based on state of the art development techniques, Kunena! is about to bring you... With an integrated plugin system that will allow you to extend Kunena beyond your wildest imaginations...
TOPIC: mysql optimization/issues
#29018
mysql optimization/issues 10 Years ago  
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]
#29018
mysql optimization/issues 10 Years ago  
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]
#29020
Re:mysql optimization/issues 10 Years ago  
Also, these recommendations for improving the FB mysql found here:
http://www.ircmaxell.com/articles/joomla/improving-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]

---------
Page: 1