Re: Searching on Two Keys with OR?

[gerald_clark]

You are ORing on two different fields.  The index cannot be used to 
check the value of z for an OR.
Why are you cross posting?

Joshua Spoerri wrote:

On Tue, 5 Aug 2003, Alexander Keremidarski wrote:
 

MySQL will never use any index for small tables. With just few rows using index
adds overhead only. Table scan is faster in such cases. This is docummented behaviour.
   

is 100,000 rows small? my simple OR queries take longer than a second.

mysql> create temporary table x (y int, z int, index (y, z));
insert into x select f1,f2 from myrealtable;
alter table x add q int;
explain select * from x where y = 1 or z = 1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 101200 rows affected (1.95 sec)
Records: 101200  Duplicates: 0  Warnings: 0
Query OK, 101200 rows affected (1.61 sec)
Records: 101200  Duplicates: 0  Warnings: 0
+-------+------+---------------+------+---------+------+--------+-------------+
| table | type | possible_keys | key  | key_len | ref  | rows   | Extra
|
+-------+------+---------------+------+---------+------+--------+-------------+
| x     | ALL  | y             | NULL |    NULL | NULL | 101200 | Using
where |
+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)


 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]