MySQL Forums
Forum List  »  Optimizer & Parser

same query sometimes very slow (punbb forum)
Posted by: Gennadiy Barvinok
Date: January 06, 2011 03:41AM

Hello,
I have a punbb forum, which uses the following query for displaying topic:
SELECT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note,
u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, LOWER(p.isocc) as isocc, p.poster_email, p.message,
p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online, IsShielded(3,p.poster_id) AS shield,
(select count(*) from reputation rp where rp.post_id = p.id) as repos, (select SUM(r1.rep_plus) from reputation r1
where r1.user_id = p.poster_id) as count_rep_plus, (select SUM(r2.rep_minus) from reputation r2
where r2.user_id = p.poster_id) as count_rep_minus FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN
groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0)
WHERE p.topic_id=14046 ORDER BY p.id LIMIT 50,50;

While this particular query works well and fast (its EXPLAIN is here -- http://www.barvinok.net/auximg/sql_right.png ) all the same query with different topic_id and LIMIT parameters works very slow:

SELECT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note,
u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, LOWER(p.isocc) as isocc, p.poster_email, p.message,
p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online, IsShielded(27,p.poster_id) AS shield,
(select count(*) from reputation rp where rp.post_id = p.id) as repos, (select SUM(r1.rep_plus) from reputation r1
where r1.user_id = p.poster_id) as count_rep_plus, (select SUM(r2.rep_minus) from reputation r2
where r2.user_id = p.poster_id) as count_rep_minus FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN
groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0)
WHERE p.topic_id=8993 ORDER BY p.id LIMIT 25050,50;

and in this case, EXPLAIN looks like that: http://www.barvinok.net/auximg/sql_wrong.png

It seems that the problem lies within 'users' table, which is defined as
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL DEFAULT '4',
  `username` varchar(200) NOT NULL DEFAULT '',
  `password` varchar(40) NOT NULL DEFAULT '',
  `email` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(50) DEFAULT NULL,
  `realname` varchar(40) DEFAULT NULL,
  `url` varchar(100) DEFAULT NULL,
  `jabber` varchar(75) DEFAULT NULL,
  `icq` varchar(12) DEFAULT NULL,
  `msn` varchar(50) DEFAULT NULL,
  `aim` varchar(30) DEFAULT NULL,
  `yahoo` varchar(30) DEFAULT NULL,
  `location` varchar(30) DEFAULT NULL,
  `use_avatar` tinyint(1) NOT NULL DEFAULT '0',
  `signature` text,
  `disp_topics` tinyint(3) unsigned DEFAULT NULL,
  `disp_posts` tinyint(3) unsigned DEFAULT NULL,
  `email_setting` tinyint(1) NOT NULL DEFAULT '1',
  `save_pass` tinyint(1) NOT NULL DEFAULT '1',
  `notify_with_post` tinyint(1) NOT NULL DEFAULT '0',
  `show_smilies` tinyint(1) NOT NULL DEFAULT '1',
  `show_img` tinyint(1) NOT NULL DEFAULT '1',
  `show_img_sig` tinyint(1) NOT NULL DEFAULT '1',
  `show_avatars` tinyint(1) NOT NULL DEFAULT '1',
  `show_sig` tinyint(1) NOT NULL DEFAULT '1',
  `timezone` float NOT NULL DEFAULT '0',
  `language` varchar(25) NOT NULL DEFAULT 'English',
  `style` varchar(25) NOT NULL DEFAULT 'Oxygen',
  `num_posts` int(10) unsigned NOT NULL DEFAULT '0',
  `last_post` int(10) unsigned DEFAULT NULL,
  `registered` int(10) unsigned NOT NULL DEFAULT '0',
  `registration_ip` varchar(15) NOT NULL DEFAULT '0.0.0.0',
  `last_visit` int(10) unsigned NOT NULL DEFAULT '0',
  `admin_note` varchar(30) DEFAULT NULL,
  `activate_string` varchar(50) DEFAULT NULL,
  `activate_key` varchar(8) DEFAULT NULL,
  `reputation_enable` smallint(6) DEFAULT '1',
  `reputation_enable_adm` tinyint(1) unsigned DEFAULT '1',
  `clone` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `users_registered_idx` (`registered`),
  KEY `users_username_idx` (`username`(8)),
  KEY `statidx` (`last_post`,`username`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8874 DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED

Any suggestions how to optimize the query?

Options: ReplyQuote


Subject
Views
Written By
Posted
same query sometimes very slow (punbb forum)
5462
January 06, 2011 03:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.