indexes for an OR based query (mysql 4.1.11)

MySQL & MariaDB Other MySQL® Questions
1

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

2

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)
union
(select * from user where nickname='hope 'limit 2)
limit 2

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

3

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)