Home > mysql > phpbb MySQL queries; need optimizations

phpbb MySQL queries; need optimizations

January 31st, 2009 Leave a comment Go to 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;

Oops, did I find a bug? no it might not be, but why does not mysql use the index in the above query, the key, key_len and ref is all null, odd yea? I dont think so, assuming that combined keys wont perform if there is a or in the query.

alter table phpbb_banlist drop index ban_ip_user_id;
Query OK, 257 rows affected (0.02 sec)
Records: 257  Duplicates: 0  Warnings: 0

alter table phpbb_banlist add index `IX_ban_ip` (`ban_ip`), add index `IX_ban_userid`(`ban_userid`);
Query OK, 257 rows affected (0.03 sec)
Records: 257  Duplicates: 0  Warnings: 0

explain SELECT ban_ip, ban_userid, ban_email  FROM phpbb_banlist_test 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_test | index_merge | IX_ban_ip,IX_ban_userid | IX_ban_ip,IX_ban_userid | 10,3    | NULL |    5 | Using sort_union(IX_ban_ip,IX_ban_userid); Using where | 
+----+-------------+--------------------+-------------+-------------------------+-------------------------+---------+------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

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

Yea, that’s it it shows the problem is gone now, and the query is never again being reported in the slow-log.

Categories: mysql Tags:
  1. Moniboniz
    February 19th, 2009 at 06:28 | #1

    Hi people… :)

  2. SpoxodoSery
    April 24th, 2009 at 07:51 | #2

    I’m the only one in this world. Can please someone join me in this life? Or maybe death…

  3. GreggG
    May 16th, 2009 at 00:50 | #3

    Hey, this is my first post.
    Can anyone tell me their opinion of the forum thus far.
    Looking to meet new people to exchange info with,so leave me your name
    Later,

  4. hoppiemochie
    May 20th, 2009 at 22:00 | #4

    Hiya all of youz!..
    How are yaz doing?

  5. BlueHornet
    July 18th, 2009 at 05:10 | #5

    What’s up, is there anybody else here?
    If it’s not just all bots here, let me know. I’m looking to network
    Oh, and yes I’m a real person LOL.

    Peace,

  6. July 18th, 2009 at 08:12 | #6

    What do you expect from a bot?

  1. No trackbacks yet.

fifty two + = fifty six