Bill, Someone sent this too the list the other day. >> MySQL's optimizer has a slight problem. OR queries cause it to get very >> confused. >> >> Try the following to get the best performance: >> >> Rewrite SELECT FROM table WHERE (condition1) OR (condition2); >> >> As: >> >> (SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE >> condition2);
walt Bill Marrs wrote: > > I've noticed that If I use an OR in my query, mysql seems to choose not to > use my indexes. Though, it would seem to help (as, if I do the query in > two steps, I can get faster results than as one query). > > Is there some way I can convince mysql to use my keys with an OR, or > perhaps another way to do queries to avoid OR, but still get an OR-like result? > > Here's a simplified example (my actual case is more complicated and slower): > > mysql> SELECT count(*) FROM Trades WHERE User1 = 79909; > +----------+ > | count(*) | > +----------+ > | 22 | > +----------+ > 1 row in set (0.00 sec) > > mysql> SELECT count(*) FROM Trades WHERE User2 = 79909; > +----------+ > | count(*) | > +----------+ > | 33 | > +----------+ > 1 row in set (0.01 sec) > > mysql> SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 = 79909); > +----------+ > | count(*) | > +----------+ > | 55 | > +----------+ > 1 row in set (0.35 sec) > > Note - the OR is slower, describe (below) even says that it doesn't use > either key in this case. > > mysql> describe SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 = > 79909); > +--------+------+-------------------+------+---------+------+--------+-------------+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +--------+------+-------------------+------+---------+------+--------+-------------+ > | Trades | ALL | User1Key,User2Key | NULL | NULL | NULL | 100775 | > Using where | > +--------+------+-------------------+------+---------+------+--------+-------------+ > 1 row in set (0.00 sec) > > Here's the table: > > CREATE TABLE Trades ( > UID int(10) unsigned NOT NULL auto_increment, > User1 int(10) unsigned NOT NULL default '0', > User2 int(10) unsigned NOT NULL default '0', > PRIMARY KEY (UID), > KEY User1Key (User1), > KEY User2Key (User2) > ) TYPE=MyISAM PACK_KEYS=1; > > Anyone know a trick to do OR queries faster? > > Thanks in advance, > > -bill > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
