Announcement

Announcement Module
Collapse
No announcement yet.

indexes for an OR based query (mysql 4.1.11)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

    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)

    Comment


    • #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)

      Comment

      Working...
      X