List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:January 8 2002 2:04am
Subject:SQL optimizer and OR - bug or misconcept?
View as plain text  
Hi!

>>>>> "Alexander" == Alexander Keremidarski <salle@stripped>
> writes:

Alexander> Description:
Alexander> When WHERE clause contains OR mysql don't any keys and don't perorm 
Alexander> validity check even.
Alexander> USE KEY directive does not change this.

MySQL will not use index if you are using OR between different keys.
(This is documented in the MySQL manual).

MySQL will use keys if you are using OR between the same keys or key
parts between the same table.

This is something that we will fix 

Alexander> How-To-Repeat:
Alexander> create table t (
Alexander>  a int unsigned,
Alexander>  b int unsigned,
Alexander>  key(a),
Alexander>  key(b));

<cut>

mysql> explain select a,b from t where a = 9258279 or b = 7973452;
Alexander> | t     | ALL  | a,b           | NULL |    NULL | NULL | 2048 | where 
Alexander> used |

<cut>

Alexander> Regardless of index schema - i used simplest example.

As you said yourself, a solution is to use temporary tables or UNION
to fix this.

mysql> alter table t modify a int unsigned not null;
mysql> explain select a,b from t where a is null or b = -10;
Alexander> | t     | ALL  | a,b           | NULL |    NULL | NULL | 2048 | where 
Alexander> used |

Alexander> In this case optimizer must notice primary key can't be null so (a is 
Alexander> null) can be skipped from where clause

MySQL should do this optimization in most cases, but apparently not
in this case :(

One reason could be that some ODBC products actually uses the construct:

WHERE PRIMARY_KEY IS NULL

to mean

WHERE PRIMARY_KEY = LAST_INSERT_ID()

(A construct that MySQL supports)

Anyway, this is not a really important or very common case that needs to be
optimized.

To solve the problem with OR between different keys, we need to add a
new optimizer module, so this has to wait until about MySQL 5.0, which
is targeted to be available by the end of this year (if everything
goes as expected).

Regards,
Monty
Thread
SQL optimizer and OR - bug or misconcept?Alexander Keremidarski7 Jan
  • SQL optimizer and OR - bug or misconcept?Michael Widenius7 Jan