Description: When WHERE clause contains OR mysql don't any keys and don't perorm validity check even. USE KEY directive does not change this.
How-To-Repeat: create table t ( a int unsigned, b int unsigned, key(a), key(b)); insert about 2000 random rows select count(*) from t; 2048 1. Check for exisiting values: mysql> explain select a,b from t where b = 9258279; +-------+------+---------------+------+---------+-------+------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+-------+------+------------+ | t | ref | b | b | 5 | const | 48 | where used | mysql> explain select a,b from t where b = 7973452; | t | ref | b | b | 5 | const | 69 | where used | mysql> explain select a,b from t where a = 9258279 or b = 7973452; | t | ALL | a,b | NULL | NULL | NULL | 2048 | where used | mysql> explain select a,b from t use key (a) where a = 9258279 or b = 7973452; | t | ALL | a,b | NULL | NULL | NULL | 2048 | where used | 2. Check for impossible (negative) values: mysql> explain select a,b from t where a = -20; | t | ref | a | a | 5 | const | 1 | where used | mysql> explain select a,b from t where a = -20 or b = -10; | t | ALL | a,b | NULL | NULL | NULL | 2048 | where used | mysql> explain select a,b from t use key (a) where a = -20 or b = -10; | t | ALL | a,b | NULL | NULL | NULL | 2048 | where used | Regardless of index schema - i used simplest example. For 1. I can agree that optimizer can decide to scan all table under certain conditions, but for 2. I think range checks must always apply. Negative values for unsigned, IS NULL for column defined not null and so on. mysql> alter table t modify a int unsigned not null; mysql> explain select a,b from t where a is null or b = -10; | t | ALL | a,b | NULL | NULL | NULL | 2048 | where used | In this case optimizer must notice primary key can't be null so (a is null) can be skipped from where clause Tested with several 3.23.x versions, 4.00, 4.0.1 Fix: Workaround in 4.x - Use UNION select ... from t where a = 'x' union select ... where b = x; mysql> explain select a,b from t where a = -20 union select a,b from t where b = -10; | t | ref | a | a | 5 | const | 1 | where used | | t | ref | b | b | 5 | const | 1 | where used | Workaround in 3.23.x - Use temporary table mysql> create temporary table tmp select a,b from t where a = -20; mysql> insert into tmp select a,b from t where b = -10; mysql> select * from tmp; 0 rows mysql> drop table tmp; lock/unlock table may be added. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
