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
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
Comment