Archive

Archive for January, 2009

phpbb MySQL queries; need optimizations

January 31st, 2009 6 comments

This one really hit me, but none would be much worried about these small things.

explain SELECT ban_ip, ban_userid, ban_email  FROM phpbb_banlist WHERE ban_ip IN ('5bcd7c03', '5bcd7cff', '5bcdffff', '5bffffff') OR ban_userid = -1;
+----+-------------+---------------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table         | type | possible_keys  | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------+----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | phpbb_banlist | ALL  | ban_ip_user_id | NULL | NULL    | NULL |  257 | Using where | 
+----+-------------+---------------+------+----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> show status like '%cost%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 54.998609 | 
+-----------------+-----------+
1 row in set (0.02 sec)

What was that, no indexes? well we should take a look, for forum like phpbb having so many users and loyal supporters, it should not be the case. Lets see the structure.

show create table phpbb_banlist;
CREATE TABLE `phpbb_banlist` (
  `ban_id` mediumint(8) unsigned NOT NULL auto_increment,
  `ban_userid` mediumint(8) NOT NULL default '0',
  `ban_ip` varchar(8) NOT NULL default '',
  `ban_email` varchar(255) default NULL,
  PRIMARY KEY  (`ban_id`),
  KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Read more…

Categories: mysql Tags:

Tunning Mysql; Rephrase queries Continued

January 31st, 2009 No comments

Hey, as of late, I had the opportunity to check the optimization for a heavily loaded phpbb system. It also shows lac of sql optimization in the code.

SELECT DISTINCT t.topic_id, t.topic_title, t.topic_poster, t.topic_first_post_id, t.forum_id, t.topic_replies, u.user_id, u.username, s.post_id, s.post_text
FROM phpbb_posts p, phpbb_topics t, phpbb_users u, phpbb_posts_text s
WHERE p.topic_id = t.topic_id AND t.topic_poster = u.user_id AND t.topic_first_post_id = s.post_id AND t.forum_id != 2 AND u.user_id != -1
ORDER BY p.post_time desc LIMIT 7;

This is one thing that is tough, and cannot be optimized without modifying the core code, and my exposure with the said script (phpbb) is too low that digging into the system will take many hours. I would recommend to design databases with the end user experience of much priority. For such things like `latest posts`, `latest news`, `new users` and other such things, keep a single table with almost the following structure

Read more…

Categories: mysql Tags:

Tunning Mysql; Rephrase queries

January 26th, 2009 No comments

The following is an excerpt from mysql slow log on a server which I was assigned to do MySQL tunning and optimizing. The following log was after the proper indexes were applied. Each query was taking about .9 seconds (query time is 0, because slow-query-time in mysql settings was 1, and that means logs will have an accuracy of seconds to the integer only)

# User@Host: articlec_bioeric[articlec_bioeric] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
select a.*,p.pen_name from ahm_articles a,ahm_penname p where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='1073534' or a.id='1073534' and a.author=p.id order by date desc limit 0,30;
# User@Host: articlec_bioeric[articlec_bioeric] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
select a.*,p.pen_name from ahm_articles a,ahm_penname p where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='1073534' or a.id='1073534' and a.author=p.id order by date desc limit 0,30;

Read more…

Categories: mysql Tags:

Consider Query Cache performance when designing tables

January 26th, 2009 No comments

I have seen many web applications with poor database design. As of recent to increase performance, every body is utilizing some sort of caching. The primary one being query cache inside database server itself. Most of the developers are not aware of this, and do not make use of this. For example lets consider an article site or a blog application. The final element article or blog will need a view count, and adding a view or hit count to the same table is what 99% are doing. This is a field which should be separated from the semi static content. Keeping the count separate and never using this count table in joins will help cache the query. Also when the table is updated, index updates will be faster.

For showing top ten etc, it would be better to use a trigger to aggregate the top ten into another table. Also for aggregate pages like front portion etc, even better would be to create a single table which will hold information from different parts of the system which will be updated using triggers, and configuration directives in db. Then the page building code will need to just read out the aggregated data and merge it into the template to output the page.

Categories: mysql Tags: