| indexes for an OR based query (mysql 4.1.11) [message #1534] |
Thu, 12 July 2007 16:16  |
 barryhunter
Messages: 6
Registered: July 2007 |
Junior Member |
|
|
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
|
|
|
| Re: indexes for an OR based query (mysql 4.1.11) [message #1619 is a reply to message #1534 ] |
Thu, 16 August 2007 11:11  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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)
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
Members
Search
Help
Register
Login
Home
... 
