PHP Trivandrum

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.

mysql