indexes for an OR based query (mysql 4.1.11)

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!


Before MySQL 5.0 you will need to union for this kind of query:

select * from user where realname=‘hope’ or nickname=‘hope’ limit 2;

Can be often replaced by

(select * from user where realname=‘hope’ limit 2)
(select * from user where nickname='hope 'limit 2)
limit 2

You will need two separate indexes on (realname) and (nickname)

Bit late, but thanks for the reply!

To follow up on this, we now on mysql5 and it uses the index_merge index, but the query still shows up in the slow query log regually.

Have also tried the union method suggested but that performed worse ( …

so have moved on to try the full text search method, in our case the table isnt updated very much so the overhead of indexing is not really an issue - will report back how that turns out… (it feels quicker and runs about 10 times quicker in standalone benchmarks, still got to see in real world usage)