MySQL Performance: MySQL => indexes for an OR based query (mysql 4.1.11)

indexes for an OR based query (mysql 4.1.11) [message #1534]

Thu, 12 July 2007 16:16

barryhunter
Messages: 6
Registered: July 2007

Junior Member

This is probably quite simple, but cant seem to figure it out

mysql> explain select * from user where realname='hope' or nickname='hope' limit 2;
+----+-------------+-------+------+-------------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys     | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+-------------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | user  | ALL  | realname,nickname | NULL |    NULL | NULL | 15838 | Using where |
+----+-------------+-------+------+-------------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

I can't get it to actually use an index for this query.

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `password` char(32) default NULL,
  `realname` varchar(128) default NULL,
  `email` varchar(128) default NULL,
  `nickname` varchar(128) default NULL,
  PRIMARY KEY  (`user_id`),
  KEY `realname` (`realname`),
  KEY `nickname` (`nickname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I've also tried both:

ALTER TABLE `user` ADD INDEX (  `realname`,`nickname`) ;
ALTER TABLE `user` ADD INDEX (  `nickname`,`realname`) ;

I've also tried this on mySQL 5.0.27 which nicely uses a union or sort_union index. So the question is what method should use for mySQL 4?

Maybe a FULLTEXT? but that sounds rather over the top!

Thanks

Report message to a moderator