# indexes for an OR based query (mysql 4.1.11)

**URL:** <https://forums.percona.com/t/indexes-for-an-or-based-query-mysql-4-1-11/382>\
**Category:** Other MySQL® Questions\
**Created:** [July 12, 2007, 2:46pm UTC](https://forums.percona.com/t/indexes-for-an-or-based-query-mysql-4-1-11/382 "2007-07-12T14:46:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![barryhunter](https://avatars.discourse-cdn.com/v4/letter/b/6a8cbe/32.png) [@barryhunter](https://forums.percona.com/u/barryhunter)\
**Post date:** [July 12, 2007, 2:46pm UTC](https://forums.percona.com/t/indexes-for-an-or-based-query-mysql-4-1-11/382/1 "2007-07-12T14:46:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 16, 2007, 9:41am UTC](https://forums.percona.com/t/indexes-for-an-or-based-query-mysql-4-1-11/382/2 "2007-08-16T09:41:38Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![barryhunter](https://avatars.discourse-cdn.com/v4/letter/b/6a8cbe/32.png) [@barryhunter](https://forums.percona.com/u/barryhunter)\
**Post date:** [December 16, 2007, 8:38pm UTC](https://forums.percona.com/t/indexes-for-an-or-based-query-mysql-4-1-11/382/3 "2007-12-16T20:38:51Z")

</div>

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)