Bug #17640 wrong where clause optimization
Submitted: 22 Feb 2006 10:18 Modified: 13 Nov 2007 17:17
Reporter: Andre Timmer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0.18 OS:Any (All)
Assigned to: CPU Architecture:Any

[22 Feb 2006 10:18] Andre Timmer
Description:
The order of where clause execution cannot be overridden.
There is no hint for to influence behaviour.

How to repeat:
select
      bb.bkwi_id
,     bb.bkwi_volgnr
,     bb.bkwi_persoonsnr_marktselect
,     aa.achternaam                 
,     aa.titels_achter
,     bb.code_functie
,     cc.omschrijving
from
      contactpersoon aa
      join      functie     bb  on (bb.bkwi_persoonsnr_marktselect = aa.bkwi_persoonsnr_marktselect)
      left join ref_functie cc  on (cc.code = bb.code_functie)
where exists (
             select ''
             from   adres xx
             where  xx.bkwi_id     = bb.bkwi_id
             and    xx.bkwi_volgnr = bb.bkwi_volgnr
             and    xx.code_gemeente in (0307, 0313, 0327, 0351)
             )
and   exists (
             select ''
             from   vestiging xx
             where  xx.bkwi_id     = bb.bkwi_id
             and    xx.bkwi_volgnr = bb.bkwi_volgnr
             and    xx.ind_economisch_actief = 1
             );

In the explain plan the second exists is evaluated first.
The optimizer makes this decision, i think, because:
- the first exists joins on primary key
- the second exists joins only on first 2 columns of 5 column primary key

The problem with this is that the first exists in this query is highly selective and the second isn't.             

Suggested fix:
A hint to influence behaviour.

For example:
select  ..
from    ..
where  use order ..
[22 Feb 2006 10:48] Valeriy Kravchuk
Thank you for a problem report. Can you, please, send a dump of tables with data to show the behaviour you described? SHOW CREATE TABLE and SHOW TABLE STATUS results for all the tables involved are needed anyway.

Have you ANALIZEd the tables in question?

If optimizer select obviously incorrect order of execution, and we will prove that, it will be a bug to fix. If you just want to ask about the ability to add hints for optimizer, add a separate feature request for that.
[22 Feb 2006 12:21] Andre Timmer
Sorry, the optimizer used the right exists condition first.
It seems to read the where clause from top to bottom :-).
I was confused because the explain plan is not ordered on id.

Maybe you can change the order of the rows in the explain plan.

+----+--------------------+-------+--------+--------------------------------+------------+---------+------------------------------------+------+--------------------------+
| id | select_type        | table | type   | possible_keys                  | key        | key_len | ref                                | rows | Extra                    |
+----+--------------------+-------+--------+--------------------------------+------------+---------+------------------------------------+------+--------------------------+
|  1 | PRIMARY            | aa    | ALL    | PRIMARY                        | NULL       | NULL    | NULL                               |    6 |                          |
|  1 | PRIMARY            | bb    | ref    | functie_i1                     | functie_i1 | 4       | bbr.aa.bkwi_persoonsnr_marktselect |    1 | Using where; Using index |
|  1 | PRIMARY            | cc    | eq_ref | PRIMARY                        | PRIMARY    | 2       | bbr.bb.code_functie                |    1 |                          |
|  3 | DEPENDENT SUBQUERY | xx    | eq_ref | PRIMARY                        | PRIMARY    | 6       | bbr.bb.bkwi_id,bbr.bb.bkwi_volgnr  |    1 | Using where              |
|  2 | DEPENDENT SUBQUERY | xx    | ref    | adres_uk,adres_ref_gemeente_fk | adres_uk   | 6       | bbr.bb.bkwi_id,bbr.bb.bkwi_volgnr  |    1 | Using where              |
+----+--------------------+-------+--------+--------------------------------+------------+---------+------------------------------------+------+--------------------------+
[23 Feb 2006 10:05] Valeriy Kravchuk
So, we can call this a (reasonable) feature request: EXPLAIN should output results ordered by id. Maybe, manual (http://dev.mysql.com/doc/refman/5.0/en/explain.html) should be also corrected, because now it just says:

"The tables are listed in the output in the order that MySQL would read them while processing the query."

Do you agree with me?
[23 Feb 2006 10:08] Andre Timmer
I do, thanks.