OR in query doesn't use keys?

[Bill Marrs]

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]