Category: mysql

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;

Continue reading “phpbb MySQL queries; need optimizations”

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

Continue reading “Tunning Mysql; Rephrase queries Continued”

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] @ www.jijutm.com []
# 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] @ www.jijutm.com []
# 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;

Continue reading “Tunning Mysql; Rephrase queries”

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.

At several stages, I have been asked to benchmark web applications. I always tend to do the light weight stuff at the start, and when it gets into my mind, that the system which is being tested is working some what like it is expected, I steer my course and go for bigger feats. For instance the RatingHQ, was run with a test of hundred thousand registered users and about the same amount of items in each of the sections before releasing for public beta.

Having said the above, the most important matter is that seldom do the programmer check his / her code more than the unit or functional tests. And code thus created would crumple when subjected to higher loads. Several times I have seen code written which simply works, but will break, and take the server that hosts it too down to it knees.
Continue reading “Responsible use of Indexes”